Query: find unmatched (from 2 other queries)

[kdorian]

I am trying to design a query to locate missing records in a table in Access 2003.

I have a table of bills linked to a table of vendors. I have been trying to design a series of queries that would let me build a list of any missing bills, so we would be able to quickly find vendors without bills for any month. I suspect I'm making some sort of obvious mistake that I'm just not seeing.

Short version: I've built 2 queries. One creates a list of all possible bill month & vendor combinations (15 records). The second creates a list of bill months & vendors that have actually been entered (10 records-the test data has gaps built in). When I create a third query using the "Find Unmatched Query Wizard" with the first table containing the results I want, and the second containing the related records, I get no results instead of the 5 I am expecting. Any idea what I'm doing wrong?

tl;dr version: I started with a query ("Unique Bill MM-YYYY") that pulled unique month-year combinations from the table of bills, and in second query ("All Vendors-All Possible Months") combine that with the list of Vendors to make a list of all possible bill months for all vendors. This query has 15 results, from 3 vendors and 5 possible bill months in the test data.

I built third query ("Existing Bills") as a stand-in for the actual bills table, where the bill date was converted to MM-YYYY format to let me match against the second query. This query has 10 results with my test data.

I have repeatedly created queries to find the records in "All Vendors..." (as the table containing the results I wanted), that did not have a match in "Existing..." (as the table with related records), and get no results where I should get 5.

I first tried using both the Vendor ID# and Billing month in both tables with links from "All V..." to "Exist..." for Vendor ID & billing month, with all records from "All Vendors..." and only matching records from "Existing..." where Vendor ID for the existing bills was null. When that didn't work I created a column in both "All V..." and "Exist..." that merged the bill month and vendor ID into one column and tried again, comparing the merged column for both, where it is null for existing bills, but I'm still not getting any results.

Any pointers on what I need to fix?

 

[Joe4]

If I understand your question correctly, you should be able to do this pretty easily with two queries (or one query with subquery).

First Query - limit the Bills to the month you want to look at

Second Query - do an Unmatched Query between your Vendors table and your first query

 

[SydneyGeek]

I think you may find this useful.
There are a few ways to acheive what you need. The first in this tutorial is much like the approach Joe mentioned. Relatively easy to set up but can run out of steam if you need to compare an additional dataset. The second approach in the tutorial involves some code but you can expand it quite easily for extra lists.

Denis

 

[kdorian]

I founnd the problem - in my find unmatched query, I was trying to find "is null" on a built field that contained a symbol ("-") in the build, and for some reason the fields that should have been blank all had that symbol. I changed the field I was searching for blanks and it worked fine.

Thanks for your suggestion!

 

[kdorian]

Thanks for the link, that looks very useful - I've bookmarked it!

My problem turned out to be something completely different (see my above reply), but thank you very much for your help!