Add new records based on parameter value and Query

[rachello89]

I have two tables called Enrollments and Payments. Because of our recuring billing, each enrollment can have multiple payments. They have a one to many relationship.

Is there a way that I can find all the records in the Enrollments table where the EndDate is null, and add a new payment record with a date that I can enter with a parameter value? So, if I have 5 enrollments that have an end date null value, then 5 new records would appear in the payments table each with the same date that I specify and all linked to their respective enrollments.



If it is not possible, please suggust alternative methods. Thanks!

[pbaldy]

An append query:

INSERT INTO Payments (Field1, Field2...)
SELECT Field1, Field2...
FROM Enrollments
WHERE EndDate Is Null

In your SELECT clause you can refer to a form to get the date value.

[rachello89]

Thanks so much for your reply. I have never worked with append queries before, and I was wondering if you could be more specific in your directions. In Query design view, where do I need to enter the everything?

[pbaldy]

Start a select query on the enrollments table, then switch it to an append query. It will ask for the destination table. Add the criteria and point to the form control for the date and you should be good to go.

[rachello89]

Thanks for your help! It worked!

[pbaldy]

Happy to help, and welcome to the site!