Editing data in multiple join query

[Guest]

I have three tables that I need joined and still be able to edit the data in
a query.
The primary table has the joining field as the key field and the other
secondary tables are autonumbered key fields with the joining field as a
standard field.
I can join either of the two tables separately with the primary table and
edit without any problem. I only need to edit data in the secondary tables.
Once I add the 3rd table, it locks all edits in the query.
I have tried pretty much everything I can think of to get this to work.
Is there any way possible to get this to work. It would save me.

Here is the SQL from primary and one secondary - edits fine.
SELECT [Peachtree Purchase History].[PO No], [Peachtree Purchase History /
Notes].[Part Note], [Peachtree Purchase History / Notes].[Proforma Received],
[Peachtree Purchase History / Notes].[Proforma Received Date], [Peachtree
Purchase History / Notes].GUID, [Peachtree Purchase History].GUID
FROM [Peachtree Purchase History] LEFT JOIN [Peachtree Purchase History /
Notes] ON [Peachtree Purchase History].GUID = [Peachtree Purchase History /
Notes].GUID;

And here is the SQL with the 2nd secondary table added - no editing.
SELECT [Peachtree Purchase History].[PO No], [Peachtree Purchase History /
Notes].[Part Note], [Peachtree Purchase History / Notes].[Proforma Received],
[Peachtree Purchase History / Notes].[Proforma Received Date], [Peachtree
Purchase History / Notes].GUID, [Peachtree Purchase History].GUID, [Vendor
Shipments/Parts].[Qty Shipped]
FROM ([Peachtree Purchase History] LEFT JOIN [Peachtree Purchase History /
Notes] ON [Peachtree Purchase History].GUID = [Peachtree Purchase History /
Notes].GUID) LEFT JOIN [Vendor Shipments/Parts] ON [Peachtree Purchase
History].GUID = [Vendor Shipments/Parts].[PO GUID];

Please let me know - thank you!!

 

[Guest]

Create a form out of the primary table. Then create subforms out of the other
two. Then you may be able to modifiy data using this from.

Almost certainly the LEFT JOIN is going to make this query not updateable.
About the only way have a query that is editable with three tables is if the
three tables are joined together properly in the relationship window with
referiential integrity enabled. Even then it can be dodgy.

 

[Guest]

Hi Jerry,
Thank you for the response.
I'm already using the final query in a subform so the goal is to be able to
edit data in the subform.
I have tried every way I can think of for joining the 3 tables and getting
them to edit in a query.
How do you suggest they be properly joined to get them to be updatable?
Thanks again.

Create a form out of the primary table. Then create subforms out of the other
two. Then you may be able to modifiy data using this from.

Almost certainly the LEFT JOIN is going to make this query not updateable.
About the only way have a query that is editable with three tables is if the
three tables are joined together properly in the relationship window with
referiential integrity enabled. Even then it can be dodgy.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have three tables that I need joined and still be able to edit the data in
a query.
The primary table has the joining field as the key field and the other
secondary tables are autonumbered key fields with the joining field as a
standard field.
I can join either of the two tables separately with the primary table and
edit without any problem. I only need to edit data in the secondary tables.
Once I add the 3rd table, it locks all edits in the query.
I have tried pretty much everything I can think of to get this to work.
Is there any way possible to get this to work. It would save me.

Here is the SQL from primary and one secondary - edits fine.
SELECT [Peachtree Purchase History].[PO No], [Peachtree Purchase History /
Notes].[Part Note], [Peachtree Purchase History / Notes].[Proforma Received],
[Peachtree Purchase History / Notes].[Proforma Received Date], [Peachtree
Purchase History / Notes].GUID, [Peachtree Purchase History].GUID
FROM [Peachtree Purchase History] LEFT JOIN [Peachtree Purchase History /
Notes] ON [Peachtree Purchase History].GUID = [Peachtree Purchase History /
Notes].GUID;

And here is the SQL with the 2nd secondary table added - no editing.
SELECT [Peachtree Purchase History].[PO No], [Peachtree Purchase History /
Notes].[Part Note], [Peachtree Purchase History / Notes].[Proforma Received],
[Peachtree Purchase History / Notes].[Proforma Received Date], [Peachtree
Purchase History / Notes].GUID, [Peachtree Purchase History].GUID, [Vendor
Shipments/Parts].[Qty Shipped]
FROM ([Peachtree Purchase History] LEFT JOIN [Peachtree Purchase History /
Notes] ON [Peachtree Purchase History].GUID = [Peachtree Purchase History /
Notes].GUID) LEFT JOIN [Vendor Shipments/Parts] ON [Peachtree Purchase
History].GUID = [Vendor Shipments/Parts].[PO GUID];

Please let me know - thank you!!