Add records via query in a one-to-may relationship

  • Thread starter Thread starter Silvio
  • Start date Start date
S

Silvio

I have 4 tables related to each other as follow: table 1 as the parent and
table 2, 3 and 4 as child using a one-to-may relation to table 1. Each table
is related to table 1 by the primary key, the record_ID. I created a query
using table 1 and table 2 and I am able to add records, however when I add
also table 3 and/or 4 then I am unable to add records. I know I can create a
form with 3 subforms to accomplish this, but I wonder if there is a way
accomplishes this at the query level.

Can add records:
SELECT tbl1.[Record_ID], tbl1.[Calibration Date], tbl2.DOmgL
FROM tbl1 INNER JOIN tbl2 ON tbl1.[Record_ID] = tbl2.Record_ID;

Can not add record:
SELECT tbl1.[Record_ID], tbl1.[Calibration Date], tbl2.DOmgL, tbl3.Cycle
FROM (tbl1 INNER JOIN tbl2 ON tbl1.[Record_ID] = tbl2.Record_ID) INNER JOIN
tbl3 ON tbl1.[Record_ID] = tbl3.Record_ID;
 
Silvio

When you have a query that returns a recordset that is not updateable (see
Access HELP re: updateable recordset), it may be due to the lack of all the
proper record IDs/fields.

And if you have one-to-many relationships, using a mainform/subform
construction is the standard practice. Of course, because this could get
'crowded', consider using a tab control and placing one subform on each
tab...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Use LEFT join from table1 to each of the other tables.
In design view double click on the connecting line and select option to see
all records from table1 and only those from other table that match.
 
Back
Top