inserting into an updateable query

Question

I'm using Access 2000, I have a form bound to an updateable query on two tables.

Here's a simplified version of the query:

SELECT tblA.ID AS MainID, tblB.ID, tblA.ItemX, tblB.ItemY
FROM tblA INNER JOIN tblB ON tblA.ID = tblB.ID;

Column tblA.ID is type "autonumber" and is the primary key. Column tblB.ID is long integer, has a unique index, and is required. There is a 1-to-1 "enforce referential integrity" relationship from tblA.ID to tblB.ID.

Let's say the form has text boxes bound to MainID, ID, ItemX, and ItemY. The issue is that if the user starts a new row, enters a value only in ItemX (i.e. nothing to any tblB columns), and does a save, Access only inserts a row into tblA. No row gets inserted into tblB. And then the query cannot subsequently retrieve what was just inserted.

Is this normal Access behavior, or should Access be able to insert a row in tblB with only the newly generated ID value and all other tblB columns null?

Answer 1

This is normal behavior if the query has the join defined as "Only include rows where hte joined fields from both tables are equal." Try changing the join to "include all records from TableA and only those records from TableB where he joined fields are equal." To change this go into the query in design view and right click on the line leading from TableA to TableB.

Note that a 1 to 1 relationship is generally unusual in database systems. What type of tables are those?