Is this a Bug, or am I an Idiot

[Alan Forman]

I've got a table with Vehicle_ID as the Primary Key and four other tables with Vehicle_ID as a foreign field for joining. When i try to make a query based on the master table with the sub tables linking it with a one-to-many join, i lose the ability to add data to the query.

I only have to add the table in the query in the 'query design' (not add any fields) and when i go to 'View' all I get is the field headings.

Am I going mad

Oh, by the way its Access 2003, 2000 format

Thanks

 

[Mile-O]

I've got a table with Vehicle_ID as the Primary Key and four other tables with Vehicle_ID as a foreign field for joining. When i try to make a query based on the master table with the sub tables linking it with a one-to-many join, i lose the ability to add data to the query.

If you have a lot of joins then the query can become non-updateable due to its complexity.

 

[Alan Forman]

If you have a lot of joins then the query can become non-updateable due to its complexity.

Thanks for your reply but, But this happens if I only have one master table and two sub tables.

Thanks

 

[Mile-O]

Can you post the SQL of your query or, even better, a small example of the problem?

 

[Alan Forman]

test.mdb attached

Can you post the SQL of your query or, even better, a small example of the problem?

Thanks for your time.

I've zipped a test.mdb, in my testing I tried this very simple mdb to prove the fault.

3 tables, 1 master, 2 sub, joined the table1 ID field to the foreign keys in tables 2 and 3. The made a query, as soon as I add the 2nd sub table and view the query all I get are the grey field headings.

Thanks

Alan

 

[Kodo]

SELECT Table1.Table_1_ID, Table1.Data_1, Table2.Table_2_ID, Table2.Table_1_Join, Table2.Table_2_Data
FROM (Table1 LEFT JOIN Table2 ON Table1.Table_1_ID = Table2.Table_1_Join) LEFT JOIN Table3 ON Table1.Table_1_ID = Table3.Table_1_Join;

 

[Alan Forman]

SELECT Table1.Table_1_ID, Table1.Data_1, Table2.Table_2_ID, Table2.Table_1_Join, Table2.Table_2_Data
FROM (Table1 LEFT JOIN Table2 ON Table1.Table_1_ID = Table2.Table_1_Join) LEFT JOIN Table3 ON Table1.Table_1_ID = Table3.Table_1_Join;

Sorry to be an idiot, where do I find that sql code please.

Thanks

 

[Alan Forman]

You cannot include tables with separate 1-m relationships in a single query. Use a form for the 1-side table and subforms for each many-side table.

Thanks for that, I thought I was going mad.

Alan