Solved Create a Child Record from a Query that Isolates Specific Parent Records? (1 Viewer)

kengooch

Member
Local time
Today, 11:32
Joined
Feb 29, 2012
Messages
137
Is it possible to create a query or a VBA code that can read through a list of Parent records that have a Y in the Sort field and then if that is true, create a child record for that Parent record. I was trying to do it with an update query, but when I join the Parent and child tables in the query showing All Parent Records and Child records that match, I get some with 1, some with 3 some with 5 child records... So it didn't seem like I could do this with an append or an update query...

Thanks in advance for any thoughts or direction for doing this.
 

plog

Banishment Pending
Local time
Today, 13:32
Joined
May 11, 2011
Messages
11,611
Can you make it a little more specific? Provide 2 sets of data:

A. Starting data from all tables. Include table and field names and enough data to cover all cases.

B. Ending data. SHow us what data you expect to end up with based on the data in A.

Again, 2 sets of data that shows what should happen.
 

bastanu

AWF VIP
Local time
Today, 11:32
Joined
Apr 13, 2010
Messages
1,401
I assume you want to create a child record if one does not already exist. You need to create an append query based on the parent and child tables with a Left Join between the PK of the parent and FK of the child (arrow pointing from parent to child) and simply add FK field from the child table to the design grid and in the criteria row add Is Null (make sure that field has the Append To row blank).

Cheers,
 

kengooch

Member
Local time
Today, 11:32
Joined
Feb 29, 2012
Messages
137
table tStaff with fields tStfID, tStfFulNm, tStfStrt, tStfZip, tStfPh, tStfSort
and table tEvent with fields tEvntID, tEvntDay, tEvntTm, tEvntStfLnk (this field is the link to the tStaff table), tEvntType, tEvntDscrpt

tStaff Table Records like:
1, "Bunny, Easter", "123 Egg Lane", 12345, 123-456-7891, "Y"
2, "Clause, Santa", "25 Christmas Way", 98765, "987-654-3211", "Y"
3, "Frost, Jack", "-32 Degrees Str", 00000, "777-666-55555","N"

tEvents Table Like
tEvntID, tEvntDay, tEvntTm, tEvntStfLnk tEvntType tEvntDscrpt
1 1/3/2021 10:00, 1 "Egg Collection" "Go to the chicken Coup"
2 2/9/2021 13:00, 1 "Egg Collection" "Go to the chicken Coup"
3 11/2/2021 8:00, 2 "Make Toys" "Working in the Toy Shop"
4 11/15/2021 7:00, 2 "Hot Chocolate" "Make Hot Chocolate"
5 11/30/2021 12:00 2 "Check Naughty List" "Got to observation library"
6 10/5/2021 22:00 3 "Make Frost" "Cover the plants with frost"

So... If the parent record as a "Y" in tStfSort field, then write a child record that has the next tEvntID, and where tEvntDay = Date() and tEvnTime = Now(), and tEvnType says "Auto Generated", and tEvntDscrpt = "Record added automatically by system"

So when I run the Query or VBA Code it looks at t he first record in the Staff tabel and when it finds the "Y" it then proceeds to create a child record automatically that is properly linked with a date and time stamp and the fact that it was Auto Generated etc... see below. It would then do the Same thing for Clause, Santa, but since Frost, Jack had an "N" it would skip that record and go to the next one, until it had read and processed all records

tStaff Table Records like:
1, "Bunny, Easter", "123 Egg Lane", 12345, 123-456-7891, "Y"
tEvents Table
1 1/3/2021 10:00, 1 "Egg Collection" "Go to the chicken Coup"
2 2/9/2021 13:00, 1 "Egg Collection" "Go to the chicken Coup"
7 11/18/2021 14:00 1 "Auto Generated" "Record added automatically by system"
 

kengooch

Member
Local time
Today, 11:32
Joined
Feb 29, 2012
Messages
137
I assume you want to create a child record if one does not already exist. You need to create an append query based on the parent and child tables with a Left Join between the PK of the parent and FK of the child (arrow pointing from parent to child) and simply add FK field from the child table to the design grid and in the criteria row add Is Null (make sure that field has the Append To row blank).

Cheers,
So in the append query i would be sure to put the parent key in at the link field and the Date and Time functions for the time etc. Will the query know to add a unique key?
 

kengooch

Member
Local time
Today, 11:32
Joined
Feb 29, 2012
Messages
137
I don't see a place to put the values like in the update query??
 

bastanu

AWF VIP
Local time
Today, 11:32
Joined
Apr 13, 2010
Messages
1,401
Can you please create a sample db with just those two tables and your dummy records? In the Append query you leave the unique key out (assuming it is an autonumber which it should be), you include the StaffID fromtStaff and append it to tEvntStfLnk, put the calculated expression on the first row of an empty column EventDate:Date() and set the AppendTo to tEvntDay (you really should only have one field for date and time, you can use the Format function to display it in both ways as needed), EventDescrpt : "Record added automatically by system" and AppendTo to tEvntDscrpt and so on.

Cheers,
 

plog

Banishment Pending
Local time
Today, 13:32
Joined
May 11, 2011
Messages
11,611
o... If the parent record as a "Y" in tStfSort field, then write a child record that has the next tEvntID, and where tEvntDay = Date() and tEvnTime = Now(), and tEvnType says "Auto Generated", and tEvntDscrpt = "Record added automatically by system"

You said something about an issue when you JOIN the parent and child tables in your UPDATE query. But with the description above, there's no need for a JOIN or an UPDATE query.

You simply build an APPEND query using tStaff. Apply your criteria, create the fields that will produce the data to go into tEvents and set it up such that it adds records to tEvents.

Lastly, the field type is DateTime which means it can accomodate both pieces of data. You should not have seperate fields for each piece.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:32
Joined
Feb 19, 2002
Messages
42,971
I'm having a lot of trouble figuring out what you are trying to do.

Using an append query to generate the next invoice or meeting or some other recurring event is fairly common. I think your problem is that you tried to start with an update query and that isn't what you need. You need an append query and ALL the data you need for the append query needs to come from the parent table or be calculated.
 

Users who are viewing this thread

Top Bottom