How to create a query which updates a table?

[wmphoto]

Hi. my problem is that I want to create a query and then have a table which effectively always shows the results of the query. The reason for this is I want to access the results from another application, and this application can only read tables, not queries.

I tried a combination of an update query to amend records which have changed and a delete query to delete records for projects which have been marked as cancelled in the source table, but I can't find out how to create an amend query which only adds records that aren't already in the destination table.

The alternative is to use a create table query... but when I run this and it replaces the destination table with a new one, it also breaks the link between the access database at the third-party application, which renders the document in that application corrupt, meaning I have to create it again from scratch.

 

[Mr. B]

You can use an append query and use the NOT IN() method as the criteria.

Assuming you have the same, single unique value field in your source table and your destination table, you can create a query that returns the necessary fields, including the unique value field. Then as the criteria for the unique value field, create a select sql statement that only returns all values from the unique value field in the destination table and use that sql statement with a NOT IN()

Something like:
NOT IN(SELECT UniqueField FROM DestinationTable;")

Just replace the "UniqueField" with the name of the unique field and the "DestinationTable" with the actual name of the table to be updated.

Then

 

[wmphoto]

thanks a lot, I was hoping that somehow there was a way for the append query to do this.

 

[stopher]

thanks a lot, I was hoping that somehow there was a way for the append query to do this.

If your unique identifier is indexed and set to no duplicates, the you can run your append query all you like and only none-duplicates will get added. Ideally you should have a primary key as your indexed field but you can just set your own indexed field if you prefer.

hth
Chris

 

[wmphoto]

thanks a lot, yeah I have a primary key which I can use for these. giving it a try now.

 

[wmphoto]

Does this work with Access 2007? I'm getting a syntax error and realised I hadn't mentioned I was using this version.

 

[wmphoto]

I took the " symbol off the statement you suggested and it seems to work now. I can't say for certain yet, the query returns no records, which is what I would expect as I haven't created any new records since creating the destination table. The proof will be when I do add a new one.