Query is driving me crazy - I can't add a new record to it

[fraXis]

Hello.

I am going crazy over this query. But, unfortunately, with the way it is shown below, Access will not let me add a new record to the query.

However, If I remove EITHER ONE of the Temp* tables from the query (so it looks like this):

OR LIKE THIS:

Access will let me add a new record WITHOUT issue (it works as intended)

But if I have BOTH of those temp tables in my query with the relationship(s) going back to the Job_Router_Sub table, it stops me from adding a new record again.

I am not sure what I am doing wrong. I am not sure why if I have both of those tables in that query, Access stops me from adding a new record.


Please help.



This is the SQL that won't let me add a new record:

SELECT Job_Router_Sub.[Quote#], Job_Router_Sub.[Quote_OP#], Temp_JobPack_Router_StartEndDates.Scheduled_Start_Date_Time, Temp_JobPack_Router_StartEndDates.Scheduled_End_Date_Time
FROM Temp_JobPack_Router_StartEndDates AS Temp_JobPack_Router_StartEndDates_1 INNER JOIN (([Job Router] INNER JOIN Job_Router_Sub ON [Job Router].[Router#] = Job_Router_Sub.[Quote#]) INNER JOIN Temp_JobPack_Router_StartEndDates ON (Job_Router_Sub.[Quote#] = Temp_JobPack_Router_StartEndDates.[Quote#]) AND (Job_Router_Sub.[Quote_OP#] = Temp_JobPack_Router_StartEndDates.[OP#])) ON (Temp_JobPack_Router_StartEndDates_1.[OP#] = Job_Router_Sub.[Quote_OP#]) AND (Temp_JobPack_Router_StartEndDates_1.[Quote#] = Job_Router_Sub.[Quote#])
ORDER BY Job_Router_Sub.[Quote_OP#];

SQL with one of the temp tables removed (this will let me add a new record successfully):

SELECT Job_Router_Sub.[Quote#], Job_Router_Sub.[Quote_OP#], Temp_JobPack_Router_StartEndDates_1.Scheduled_Start_Date_Time
FROM Temp_JobPack_Router_StartEndDates AS Temp_JobPack_Router_StartEndDates_1 INNER JOIN ([Job Router] INNER JOIN Job_Router_Sub ON [Job Router].[Router#] = Job_Router_Sub.[Quote#]) ON (Temp_JobPack_Router_StartEndDates_1.[OP#] = Job_Router_Sub.[Quote_OP#]) AND (Temp_JobPack_Router_StartEndDates_1.[Quote#] = Job_Router_Sub.[Quote#])
ORDER BY Job_Router_Sub.[Quote_OP#];

Table schema and data in the temp tables:

The Quote# and OP# fields properties are identical in both tables.

Thanks.

 

[Gasman]

See if any of these apply.

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

 

[MajP]

I would think with your composite key and then a single key in some of the tables (I definitely do not understand that design). You will need to enforce referential integrity and ensure cascade updates. If that alone does not work then I would also try setting the query recordset property to dynaset (inconsistent updates).

Dynaset	0	(Default) You can edit bound controls based on a single table or tables with a one-to-one relationship. For controls bound to fields based on tables with a one-to-many relationship, you can't edit data from the join field on the "one" side of the relationship unless cascade update is enabled between the tables.
Dynaset (Inconsistent Updates)	1	All tables and controls bound to their fields can be edited.

 

[The_Doc_Man]

I'm not going to say that Access hates this badly enough to barf on it, but you have what appears to be a normalization violation here.

Temp_PartNumber_MASTER_Operations and Temp_JobPack_RouterStartEndDates both have OP# as their Prime Keys. IF the OP# is really the PK for both tables then they should not be in two separate tables. Their fields should all be in the same table. Normalization rules say that things depending on the same prime key should be in the same table. More precisely, if in each table, those fields depend ONLY on the PK, then it should make no difference if the fields are in the same table.

If you merge the fields in those two tables, you could EASILY reach the three-table scenario where your "New Record" option lights up for you.

NORMALLY, when Access sees what it thinks is an ambiguity, it balks at allowing the query to update. Having two tables with the same PK is at least a potential example of this problem.

One last (very tiny) quibble: Putting special characters such as the octothorpe (#) into a field name is vary rarely if EVER a good choice. If you changed to OPNum or OPN or something similar, you can omit the square brackets [] when presenting field names, and that means typing fewer characters during complex programming. Further, there might exist 3rd party code libraries you could call that would not accept the special character names.

 

[arnelgp]

I am going crazy over this query.

it is crazy alright. how can you add record to the "child" table when the "parent" record does not exists?
you need to use Form (not query directly).
the Job Router is the Parent.
Job Router Sub is the Child (Link Master Fields: [Router#], Link Child Fields: [Quote#])
and the 2 Temp table are the Children of Job Router Sub (Link Master Fields:[Quote#], [Quote_OP#], Link Child Fields:[Quote#], [OP#])