Machine Nation

Irish Politics Forum - Politics Technology Economics in Ireland - A Look Under The Nation's Bonnet


Devilish machinations come to naught --Milton
 
PortalPortal  HomeHome  FAQFAQ  SearchSearch  MemberlistMemberlist  UsergroupsUsergroups  RegisterRegister  Log in  GalleryGallery  MACHINENATION.org  

Share | 
 

 Just on the offchance - anyone know Access 2007?

View previous topic View next topic Go down 
AuthorMessage
Guest
Guest



PostSubject: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:33 am

Little Help!? Have a question about MS Access 2007 (or Access in general).

Trying to uses an "update" query - anyone familiar with it, who might be able to explain a few things to a novice?
Back to top Go down
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:39 am

basically - I just want to know is there a simple way of entering the exact same field data into multiple (new) records, bar using cut + paste 200 times?
Back to top Go down
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:44 am

You want to write a record 200 times from another record or file ? Can you run a piece of SQL in a loop ?

Sorry, it's been years since Access and that was only cursory.
Back to top Go down
Ex
Fourth Master: Growth
avatar

Number of posts : 4226
Registration date : 2008-03-11

PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:46 am

Can you not do a multiple paste to the table, like in Excel.
Back to top Go down
View user profile
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:51 am

Auditor #9 wrote:
You want to write a record 200 times from another record or file ? Can you run a piece of SQL in a loop ?

Sorry, it's been years since Access and that was only cursory.

Not exactly, the new records are as yet non-existant. The problem is probably becuase the database is badly desgined (by me)

Put simply, I have a list of members in one table, and a list of payments due/recieved (from members) in another. In this second table, I need to add a new blank record for each member with the "year" set to 2009. As each member pays, the rest of the record will get filled in with the details.

It might sound easier not to enter anything until a member pays up, but if I do that, there is NO WAY (that I have yet found) to get access to give me a list of "members yet to pay" later in the year. Only way I can get that list is to begin with a new set of blank records at the start of the year.

Sigh...


Last edited by yehbut_nobut on Tue Dec 16, 2008 2:55 am; edited 1 time in total
Back to top Go down
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:54 am

EvotingMachine0197 wrote:
Can you not do a multiple paste to the table, like in Excel.


Well one piece of data (the member number) has to change for each record, per above description.
Back to top Go down
Ex
Fourth Master: Growth
avatar

Number of posts : 4226
Registration date : 2008-03-11

PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:54 am

Well then, why not insert a new field in the payments table and call it '2009 payments' or something ?
Back to top Go down
View user profile
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 2:56 am

Quote :
It might sound easier not to enter anything until a member pays up, but if I do that, there is NO WAY (that I hve yet found) to get access to give me a list of "members yet to pay" later in the year. Only way I can get that list is to have a new set of blank records at the start of the year.
This is creating a record each time someone pays ?

Can you have a third file and do a count of new records and link to that ? Good relational databases always have a shlock of little files in them.
Back to top Go down
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:05 am

Auditor #9 wrote:
This is creating a record each time someone pays ?

Currently, I'm needing to create the record before any payment, so that the contents can be filtered between unpaid (blank) and paid.

Auditor #9 wrote:
Can you have a third file and do a count of new records and link to that ? Good relational databases always have a shlock of little files in them.

Shocked That might be a solution! (I'd need to add any new members to that list as well as the original members table wouldn't I?)


EvotingMachine0197 wrote:
Well then, why not insert a new field in
the payments table and call it '2009 payments' or something ?
I originally started like that, untill I realised I'd be addinga new field every year forever, which didn't seem the right way to go about it...?
Back to top Go down
Ex
Fourth Master: Growth
avatar

Number of posts : 4226
Registration date : 2008-03-11

PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:08 am

Actually, your members table should have a 'fee due date' field.

So that should cause a fee due box to pop up on their form page. Then a button to 'pay fee' causes an update to the payments table.
Back to top Go down
View user profile
Ex
Fourth Master: Growth
avatar

Number of posts : 4226
Registration date : 2008-03-11

PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:11 am

yehbut_nobut wrote:

EvotingMachine0197 wrote:
Well then, why not insert a new field in
the payments table and call it '2009 payments' or something ?
I originally started like that, untill I realised I'd be addinga new field every year forever, which didn't seem the right way to go about it...?

Yeh, ignore that nonsense. I think the above is more like it.
Back to top Go down
View user profile
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:12 am

EvotingMachine0197 wrote:
Actually, your members table should have a 'fee due date' field.

So that should cause a fee due box to pop up on their form page. Then a button to 'pay fee' causes an update to the payments table.

ah, I'm not that advanced! that's beginning to sound like witchcraft... Smile
Back to top Go down
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:13 am

Quote :
Auditor #9 wrote:
Can you have a third file and do a count of new records and link to that ? Good relational databases always have a shlock of little files in them.

Shocked That might be a solution! (I'd need to add any new members to that list as well as the original members table wouldn't I?)


If you know in advance how many payments then you'd need a file which can link to each member, perhaps more than once depending on how many loans the member had. The file might have a signal for monthly payments - '12' or '52' and a field for the number of payments already made - a count.

So that new third table would yes have a matching key and records for any new member who came in to match your original table. That's a bit more maintenance though.
Back to top Go down
Ex
Fourth Master: Growth
avatar

Number of posts : 4226
Registration date : 2008-03-11

PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:15 am

Or else just do it in excel and import the table.
Back to top Go down
View user profile
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:19 am

thank you very much for the advice! time for bed now, will work on it more in tomorrow.

Hope I'm not dreaming of crosstabs and primary keys...

(Where did my life go!?)

good night!
Back to top Go down
Guest
Guest



PostSubject: Re: Just on the offchance - anyone know Access 2007?   Tue Dec 16, 2008 3:30 am

Assuming you have two tables like this:

table the_member(
member_id
member_name
)

table the_payment(
payment_id
payment_member
payment_amount
payment_year
)

where payment_member in the_payment is the member_id from the_member. You would do the following query:

Code:
SELECT the_member.member_name
FROM the_member LEFT JOIN the_payment
ON the_member.member_id = the_payment.payment_member
WHERE (((the_payment.payment_year) Is Null));

- which should give you just the members who have no payment record. If you want to get the full list of members, with whether they have paid or not:

Code:
SELECT the_member.member_name,the_payment.payment_amount
FROM the_member LEFT JOIN the_payment
ON the_member.member_id = the_payment.payment_member
Back to top Go down
Sponsored content




PostSubject: Re: Just on the offchance - anyone know Access 2007?   

Back to top Go down
 
Just on the offchance - anyone know Access 2007?
View previous topic View next topic Back to top 
Page 1 of 1
 Similar topics
-
» Can anyone access bid documents?
» BAC HONORARIUM - BUDGET CIRCULAR 2007-3
» PNOC-EC to access P2-B credit line
» OIL AND GAS LAW OF THE KURDISTAN REGION - IRAQ Law No. (22) - 2007OIL AND GAS LAW OF THE KURDISTAN REGION - IRAQ Law No. (22) - 2007
» Does DHS have access review my bank account and transactions ?

Permissions in this forum:You cannot reply to topics in this forum
Machine Nation  :: Welcome and Chat :: Chatter-
Jump to: