Mass update child table

[Oregoncoder]

I have about 50 members in a parent table who are “Honored Life Members”. My child table consists of all the payments for all of the members by year.



what I would like to do is a mass record add for the “honored Life Members” in the payment table. The record shows them as paid for the year 2024.

[xps35]

What did you try so far? Why add payments if they don't have to pay?

[mike60smart]

How is the Parent Table linked to the Child Table?

[Oregoncoder]

It is in a one to many. One membership record to multiple years of payments

[Micron]

One way would be to copy the records manually and paste append them. Assuming you're using an autonumber field, then update the date to your 2024 date WHERE myIdField > whatever your last number was before the append. Copy and paste append can be wonky sometimes so I'd suggest copying the table first so that you have a backup of it.

Of course, there are more complicated methods such as writing code to
- copy the recordset and append the new records with the 2024 date using .AddNew, or
- write a sql statement that loops over a recordset and runs an update sql statement that makes use of the new date

[Oregoncoder]

Thought about creating a table of just those members and then append to the payments. As the payment is linked via member number (auto number) in the parent table. Just hoped there might be a simple query to do so.

[Micron]

You might be able to do it with one query, come to think of it. It would be an append query that uses the member table and the payments table, linked on the pk and fk (master/child) fields. The query would use a calculated field to create your date field values. While I know that you can Update 2 tables at the same time, I don't recall ever trying to append to only one when using 2 related tables. That it is possible to update makes me think it should be possible to append.

[ArviLaanemets]

It is in a one to many. One membership record to multiple years of payments

Do you mean the payments table is like
tblPayments: PaymentID, MemberID, PaymentYear1, PaymentYear2, ... !?

[Oregoncoder]

Do you mean the payments table is like
tblPayments: PaymentID, MemberID, PaymentYear1, PaymentYear2, ... !?

It is actually a record for each year. Looking to add a new record for the year 2024.

[mike60smart]

Can you upload a screenshot of the relationships window showing all your tables?

[ArviLaanemets]

So the structure of payments table is like
tblPayments: PaymentID, MemberID, PaymentYear/PaymentDate, Payment

The query (simplified one) will be something like

INSERT INTO tblPayments (MemberID, PaymentYear, Payment)
SELECT mem.MemberID, PaymentYearValue, SomeExpression As Payment
FROM tblMembers mem WHERE mem.SomeField = "Honored Life Members"

[CJ_London]

Without knowing your table and field name or structure would have thought something like this would work for a normalised database

INSERT INTO tblPayments ( memberFK, tranDate, tranAmount )
SELECT memberPK, #1/1/2024#, 50
FROM tblMembers
WHERE tblMembers.LifeMember=True

edit: Snap

[Oregoncoder]

Ignore the name field in the child table, I put there while creating the database to make sure i knew what I was doing.

[mike60smart]

Points to note:-

1. do not use name as a field name as it is a reserved word in Access.
2. do not use spaces in field names
3. you do not need the Year field as you already have the Year as part of RenewalDate
4. Enforce referencial Integrity to avoid adding child records without a Parent

[Oregoncoder]

Points to note:-

1. do not use name as a field name as it is a reserved word in Access.
2. do not use spaces in field names
3. you do not need the Year field as you already have the Year as part of RenewalDate
4. Enforce referencial Integrity to avoid adding child records without a Parent

Tanks for the advice!