General discussion


Microsoft Access - updating query data

By ka75 ·
When writing an sql query referring to one table, I have the ability to update data inside the table created by the query, for instance:
SELECT * FROM products;

When the query involves more than one table updateing data in the table created is not possible. for instance:

SELECT BI.bidNum, CO.ColorName
FROM Bids AS BI, Colors AS CO
WHERE BI.Color=CO.colornum;

Is there an possibility of writing such a query and still have the option of updating data in the table created ?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by John Gordon In reply to Microsoft Access - updati ...

I'm not sure what you are refering to by the term 'table created' as a select statement does not create a table!

You can update more than one table with a single SQL UPDATE query. If you look up the Access Help system and find the SQL reserved word UPDATE you will see the syntax for the query to update a single table. Updating more than one table is simply a matter of defining a relationship between the tables and building this into the query using a JOIN.

BTW which version of Access are you using? and how are you running the update query? Is it in VB code or via a query or a form?

If you explain what you need to do in plain English and give me the names of the tables and relevant fields I will try to write the SQl code for you.

Collapse -

by ka75 In reply to Microsoft Access - updati ...

in access when you double click a query that you have created the result is shown as a table.

SELECT * FROM table_name;

save the query and double click it after saving. you get the results in a table equal to the original table 'table_name'. at that moment you can change data in the table created by the query, by writing or deleting data inside the cells.

example 2:

select T1.d1, T1.d2, T1.d3, T2.d2, T3.d2

in this case the query has an error, I don't know why. this query should be able to produce a result that could be updated by writing and deleting data inside the table cells, and without the need of a new query

Collapse -

by John Gordon In reply to Microsoft Access - updati ...

The correct SQL code for the query you posted is:

SELECT T1.d1, T1.d2, T1.d3, T2.d2, T3.d2
FROM (T1 LEFT JOIN T2 ON T1.d2=T2.d1)
LEFT JOIN T3 ON T1.d2=T3.d1;

The first join is enclosed in parenthesis! This will display the data you want.

You cannot update the data from this query as this is not permitted for the situation where the query is based on three or more tables in which there is a many-to-one-to-many relationship, which it is in your case. See Microsoft Knowledge Base Article - 304473

Related Discussions

Related Forums