How to reference data that does not exist (1 Viewer)

[tigernick]

Hi,

I have an access database with 8 tables and as many queries and forms. Our company uses an application that performs backups of other companies data, this data is imported into access into one table. The backups are scheduled to run overnight, but sometimes they do not start for whatever reason. We need to use a query to identify when a backup does not start, so therefore to identify when data does not appear or exist in the table.

This problem is only concerned with two tables. In one table i have the backups policies (Backup Policy) and the details about when the backup should run. In the other, the main table (Backup) is the actual data that has been imported. So far I have queried only the backup policies that should run, but i need to go one step further and compare this to data in the Backup table that does not exist.

Im not sure whether this should be a bit of code in the query that compares data in the Backup Policy table with that in the Backup table when it is not present.

Ideally i need something that says. "If a particular policy name does not appear in any record in the Backup table, report back a list of policy names."

OR

(Lookup a list of policy names in table backup policy,

if these policy names do not appear in the backup table, report back a list of the policy names,

if these policy names appear in the backup table, do not report back a list of the policy names)

If anyone has any ideas if this is possible, and how, I would be most grateful. Also, if it involves the use of code or functions etc I may need a little bit of guidance!!

Let me know if you need any more details of the table structures.

 

[neileg]

Use the unmatched query wizard.

 

[tigernick]

Thanx for that. While I understand that concept there is one more thing. When running this query there are no records displayed, I know why this is becasue I need to restrict the search on the Backup table to particular days or after a particular date. I am using the policy field which is the pk of table backup ploicy and fk of table backup to link the two tables. As things are every policy in the Backup policy table has a related policy in the backup table, so i need to restrict the search to after a particular date.

I have a date field in the table and i know how to display only those records after a certain date, but so far i cannot combine the two. Any ideas? When i try to add in the date field to the query it still displays 0 records.

 

[neileg]

Create a query from each table showing only those records and fields that you are interested in. Then run the unmatched query wizard on your two queries.

 

[tigernick]

Thats brilliant thanks v much, ive been pulling hair out all day!!: