Update Query (1 Viewer)

[rhernand]

I have a Prod Table and a TEST Table, both are defined the same; same design, same key. They both have most of the same data records, except the Prod has more. I want to link both tables and update the TEST with the records in the Prod that are not in TEST.

 

[Matty]

I think you need to do an Append Query instead. First you need to do a Find Unmatched query to find the extra Prod records and use those results in an Append Query to add those records to TEST.

If you were needing to edit existing records, you would need an Update Query.

 

[rhernand]

append

Yes, I do want to append the records from PROD to TEST, not in TEST. I have created an Append query, but how will it know to append the records not in TEST.

 

[Matty]

You'll have to first make a query to find the records that are in PROD but not TEST. That will give you the records to append.

 

[boblarson]

Use the unmatched query wizard to create a query to find the records in the one, but not in the other. Then, use that as the basis as the append query.

 

[rhernand]

append query

I am running the unmatched query now, it will probably run awhile. After it finishes, how will I select those umatched records to append to the table?

 

[boblarson]

If you have the unmatched query selected in the database window (where the list of saved queries are) , you can just click INSERT / QUERY / DESIGN VIEW and it will put the unmatched query into the QBE where you can select all of the fields, click the menu items QUERY / APPEND QUERY to change the current query to an append query and then you can run that to append the unmatched query items.

 

[rhernand]

I get it. One question, after I created the unmatched query, I looked at the Design View and it puts IsNull for that one field I chose. If my key is five fields can I put them in the query and make the Criteria on those IsNull also?

 

[boblarson]

Don't change any of the criteria, just pull the fields in from the unmatched query as it's already chosen in the unmatched query.