changing records in simple select query

[shopwise]

I have 2 tables both containing identical structure and that include a "fax number" field and a "remove" yes/no checkbox field.

I have created a simple select query with the following SQL:

SELECT faxnumbers.SoldToFax, faxnumbers.Remove
FROM faxnumbers INNER JOIN current_removals ON faxnumbers.SoldToFax = current_removals.SoldToFax;

When I attempt to make a change to the remove field changing it from no to yes, it is not allowed.

how is this corrected so that i can make changes to the underlying tables

 

[dhookom]

Are either of the SoldToFax field primary keys? If not, can they be made primary or at least unique indexes? If they can't, how does the join understand the exact relationship between the tables?

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

To elaborate a bit on Duane's response ...

When you attempt to change a field in a query, the system needs to uniquely identify the record in the base table that needs changing.

Without a primary key in the SELECT fields it has no mechanism to do that. SQL queries do not retain any "hidden" information about which base table records contributed to a result record in the query.

Can you include primary key fields in your query to allow the system to uniquely identify the source (i.e. base table) records to update?

 

[BitZero]

Try this:

SELECT faxnumbers.*
FROM faxnumbers
WHERE faxnumbers.SoldToFax in (select SoldToFax from current_removals);

This bypasses the JOIN and allows you to do an update thru the query. With the JOIN, you could end up with a particular record in the faxnumbers table appearing multiple times in the result, and therefore an update would not be allowed.