Need to add complex criteria to a query

[glawson]

Hi,

I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".

I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.

I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...

SELECT [Dove Data File].*
FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase
WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL
FROM Dove
WHERE [Dove Data File].TowerBase = Dove.TowerBase
AND [Dove Data File].Bells <> Dove.Bells))=True));

But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.

I hope you can make sense of that and thanks in anticipation.

Gareth

 

[Pat Hartman]

Rather than an Exists subquery, use a left join.

If you split the data into two tables - Active and Inactive, there should NEVER be any updating of records in the Inactive records table. The only update would be via append query. You would select data you wanted to archive from the Active table and append it to the Inactive table and then delete it from the Active table. So, any given record exists in one and only one place.