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?

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?

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.

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

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...

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.

Well then, why not insert a new field in the payments table and call it '2009 payments' or something ?

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.

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.

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...?

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.

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.

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...

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.

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.

Or else just do it in excel and import the table.

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!

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