Add records

[krester]

Hi everyone,
I have 2 tables: tblMembers and tblPayments
tblMembers: member_id, f_name, l_name, active
tblPayments: member_id, payment_code, month, year, sum …
At the beginning of each year, I would like to add records for all active members for each month in tblPayments.

Anyone got any ideas?​

 

[highandwild]

Heard of cartesian products?

Set up a table with the 12 payment dates in a date field.

Set up a query that uses the members table and the date table BUT do NOT join them. Add the fields that you want to append to the peyments table including the date field.

When you run the query, you will get a dataset which has 12 records for each member, one for each of the payment dates.

Change the query to an append query and append the data to the payments table. There you go.

Need an example or will this do?

 

[krester]

Hi highandwild,
First, thank you for your help. I try it and it really work but I want that after that the user will choose a year and click a button, the records will be added according to the selected year.
Thanks in advance.​

 

[highandwild]

Link the query to a combo box or text box on the form that contains the year.

 

[krester]

highandwild - it's working, so simple and elegant (I thought that I must use VBA).
Thank u very much !

 

[highandwild]

Glad to be of service Krester.