How can I modify this query design to make it updatable?

[buratti]

I am trying to create a query consisting of 3 different tables. All tables have a relationship on the CustomerID field. My problem is that every variety of design I come up with, the query is still NOT updatable and I need it to be.

Here's the strange thing... When I create this query in the query design window and IF I exclude adding any fields from the [Recurring Charges] table, and then view its data , I cannot add/edit any data. But after opening the query and clicking on any column name and sort ascending/descending, after the refresh/resort, the data now IS updatable. Strange?!?!? But if I add back some fields from the [Recurring Charges] table, then regardless of the strange sorting issue above, the data is NOT updatable.

I have tried every variation on joins even though some returned results unusable, but nonetheless every variation was still NOT updatable.

Here is the query:

 SELECT [Updated Card Info].*, [Customers Extended].*, [Recurring Charges].*
FROM ([Updated Card Info] LEFT JOIN [Customers Extended] ON [Updated Card Info].[Customer ID] = [Customers Extended].ID) LEFT JOIN [Recurring Charges] ON [Customers Extended].ID = [Recurring Charges].[Customer ID];

Any suggestions on modifications that WILL make this query updatable.

I tried keeping this as basic of a question as possible but if more information is needed I can provide it.

Thanks in advance.

 

[MarkK]

You might be able to make the query updateable if you only use INNER JOINs (as opposed to LEFT JOINs or RIGHT JOINs) and if you don't enforce referential integrity on the relationships between the tables.

But more typically, I would design a user interface such that each form is only bound to a single table, and use a subform if you need to show and edit child records. Drive the subform with a single table also, and then you never run into this updateable query problem.

It's way more common to make multiple-table queries when you write reports, or when you do statistical summaries on your data.

Hope this helps,

 

[buratti]

Thanks... Inner joins yields the same results as described originally. In all honesty this query data is not intended for a form/report to be bound to. It is intended to be manipulated via VBA code (opened as a VBA recordset) and data updated within that code.

 

[MarkK]

Well in that case update each table individually and use UPDATE queries, not SELECT queries. It'll be way faster and more reliable.

UPDATE TableA
SET Field1 = <value1>, Field2 = <value2>
WHERE TableA_ID = <ID_value>