Create a table for each record in another table

[ka3pmw]

I am trying to create an access 2007 database for Windows 7 that tracks payment dates for club members. I need a record for each date that the member paid. Each member has a separate record in a table called Roster.
I want to take three fields from roster and put them in an individual table for that member's dues payment. These are Ham Radio operators but the problem is that not all members have a CallSign, and some share the same Last name. The fields that I have to identify them are Callsign, LastName, and FirstName.
I hope I can do this automatically and not have to do it one at a time as there are over 140 members at this time.
I have been trying to figure this out for 3 days. Can someone PLEASE point me in the right direction?

 

[Ranman256]

You would not create a table for each record. You should put the dates in a tPayment table, using MemID from the tMember table. This would be an autonumber in tMember.

Tho in tPayments table, the MemID is LONG integer.

 

[plog]

Ran addressed 1 issue you have (the one you must first fix): You need an autonumber primary key (https://support.office.com/en-us/ar...mary-key-07b4a84b-0063-4d56-8b00-65f2975e4379) in your roster table.

You need a way to uniquely identify each person in roster. You've looked at your data and figured out Callsign, Fname and Lname does that for you. But its not a good idea to go basing uniqueness on things that could change (women get married, people can change callsign, etc.). So what you should do is simply add a number to every record and then use that number to uniquely identify them. That way, in the payment table, you simply put the number and then you can use your roster table to determine who that actually is.

Now, for your payments issue. You would us an APPEND query (https://support.office.com/en-us/ar...nd-query-71cd4fe3-c2d7-4856-a0c8-a2638ccf4ad0). It would be based on your roster and would allow you to put a set of records into your payment table for each person (or even just people who meet certain criteria).

Give my links a read, try to work through this and post back with any issues you have.

 

[ka3pmw]

Can I use the auto numbered key as the unique key for the payment table?

 

[plog]

Its an autonumber data type. And yes you can use one as the primary key for the payment table.

 

[ka3pmw]

Thanks, I'll try again. I must be reading that article wrong. I can't get it to work.

 

[plog]

What do you have? Can you zip and upload your database?

 

[ka3pmw]

Here it is. I deleted the records in it. I works to the point of putting in the payments.

If you need, I can put in a couple od fictitious records.

 

[plog]

You put autonumber primary keys in your tables, but your not using them properly. You need to use them as foreign keys (https://en.wikipedia.org/wiki/Foreign_key) in tables they have a relationship with. That means instead of CallSign, LastName, FirstName in Dues, you simply have a RosterID field. That field would hold the ID from Roster for a person. That's it. With that one number you now will know everything in the Roster table about who paid in the Dues table.

I see some other issues as well, all in the Roster table:

1. LastPaid shouldn't be a field in a table. It's a calculated value, and by that I mean you can look at other data (specifically the Dues table) and determine it. You don't store calculated value, you simply calculate it in a query when you need it.

2. I think you are storing values in field names. I don't know what ARRL, ARES, RACES, Skywarn are for, but my guess is they shouldn't be field names but instead values you add to a field. Can you explain what those 4 fields are for?

 

[ka3pmw]

Well, I read that link and followed it and I read several other links and followed them. I have one hair left on my bald head and right now it is trembling in fear of getting pulled out.

For the life of me I can't get the data from the Roster to the dues table. I have tried a query to populate the dues table but to no avail.

I wish I still had my memory, I can remember events from years ago, but nothing recent.

I have enclosed what I have so far.

 

[ka3pmw]

I finally got the table to populate. Here is the new one.

I have 2 records in it, My Wife's and mine.

 

[AccessBlaster]

See if this helps with your hair problem. I took a few liberties with your design, for illustration purposes.

 

[plog]

Looking better. Sort of. You've confused 2 fields into 1 in the Dues table.

MemberNumber should be a field in Dues, but it should not be an autonumber nor the primary key. It should be just a regular number. This will hold the ID field value from Members.

You should have an autonumber primary key in Dues. I would call it Dues_ID. So basically, rename MemberNumber to Dues_ID and then add a new number field called MemberNumber.

Then, delete all the records in Dues. Then take this SQL below and paste it into a query:

INSERT INTO dues ( MemberNumber, DatePaid )
SELECT Roster.ID, Date() AS DatePaid
FROM Roster;

Run that query and it will put a record in Dues for every record in Roster.

Final and most important note: Don't do any of the above in your actual database. Make a copy and play around with that copy until you understand all that we have done. Then, when you feel comfortable with everything we can move it to the actual database.

 

[ka3pmw]

I think I have it unless you suggest doing your suggestion about: MemberNumber should be a field in Dues, but it should not be an autonumber nor the primary key. It should be just a regular number. This will hold the ID field value from Members.

You should have an autonumber primary key in Dues. I would call it Dues_ID. So basically, rename MemberNumber to Dues_ID and then add a new number field called MemberNumber.

Then, delete all the records in Dues. Then take this SQL below and paste it into a query:


Code:
INSERT INTO dues ( MemberNumber, DatePaid ) SELECT Roster.ID, Date() AS DatePaid FROM Roster;
Run that query and it will put a record in Dues for every record in Roster.

It is working as far as I can determine. When I choose add member or edit member, the sub form comes up with the form and I can add and edit it as needed.

What do you think?

Thanks for the help and Merry Christmas and Happy New Year to you and yours!

 

[plog]

I don't know. Looks like the tables are correct. If the subforms are operating correctly for you, then seems like its resolved.

 

[ka3pmw]

I'd say so. Now all I Have to do is figure out how to close this thread!

 

[ka3pmw]

All of a sudden all of my dropdown boxes will not update.

I update them, go to the next record then come back and they are just as they were before updating.

This happens when I run look up. Anyone have an idea why?

 

[plog]

I'm not the best with forms. I would post in the Form thread about this issue.