Update Query

[Guest]

Can i create an update query to add a new record with one filed (location ID)
to a table if it is not present. For example i have a table of locations
visited in a specific year. I need to allow users to create estimates from a
query based on those locations then create a table from the query. After the
create table query is run once the data could be manually changed in the
newly created db. Problems occur when locations may be added to the quote db
after the create table query is run. I want an update query to add the
location to the new db without changing fields that may or may not have been
modified. All i need to add is one field with a location ID.

 

[David Seeto via AccessMonster.com]

So long as you have the Location IDs that are missing from the table in
another table, you should be fine - it sounds like this is the case for you.

First, you're going to need to identify which locations are missing. You do
this with a query that looks like:
SELECT tblLocations.*
FROM tblEstimates RIGHT JOIN tblLocations ON tblEstimates.LocationID =
tblLocations.LocationID
WHERE (((tblEstimates.LocationID) Is Null));

Then, you can use this as the basis of an Append query:
INSERT INTO tblEstimates ( LocationID, Cost )
SELECT qMissingLocations.LocationID, 0 AS ZeroCost
FROM qMissingLocations;