Query to pull all records for those missing records

[lioneyes]

Hi. I have a DB of payment records, each with a payment number (not
autonumbered because they are specific to date) and a company no.

Would like to pull a report of all payment records for each company that is
missing a specific payment number. Say all payment records for any company
missing payment #10.

I got help from someone else about using a totals query as the source of
another query to pull all payment records for any company that had fewer
(count) then a specific number of payments but now need to pull them if they
are missing a specific payment number.

We are tying to validate a large amount of data.

thanks

 

[Allen Browne]

Again, use a Cartesian Product to give you every payment number for every
customer, and then use a frustrated outer join to identify the missing
numbers.

1. Create a query that gives you just all the valid payment numbers. It
might look like this:
SELECT DISTINCT PaymentNumber FROM Table1;

2. Create a query with 2 input tables:
- your Customer table
- the query you just saved.
There will be no line joining the 2 'tables' in the upper pane of query
design. This is a Cartesian Product, giving you every combination of
customer and payment number.

3. Use the Unmatched query wizard (in the first dialog when you create a new
query) to get all the records in the query you just saved that have no match
in your payments table.

 

[Klatuu]

SELECT [CompanyNumber] FROM tblCompany WHERE [CompanyNumber NOT IN (SELECT
DISTINCT[CompanyNumber] FROM tblPayments WHERE [Payment Number = 10)

The second select is known as a sub query. It will return a list of every
company that does have a payment number 10. Using it in the NOT IN clause
causes the first select to return a row for each company number that is not
in the second select, so now you have a list of companies that do not have a
payment number 10.

 

[lioneyes]

Interesting solutions. I will give them a try. Thanks very much to both of
you.

SELECT [CompanyNumber] FROM tblCompany WHERE [CompanyNumber NOT IN (SELECT
DISTINCT[CompanyNumber] FROM tblPayments WHERE [Payment Number = 10)

The second select is known as a sub query. It will return a list of every
company that does have a payment number 10. Using it in the NOT IN clause
causes the first select to return a row for each company number that is not
in the second select, so now you have a list of companies that do not have a
payment number 10.

Hi. I have a DB of payment records, each with a payment number (not
autonumbered because they are specific to date) and a company no.

Would like to pull a report of all payment records for each company that
is
missing a specific payment number. Say all payment records for any
company
missing payment #10.

I got help from someone else about using a totals query as the source of
another query to pull all payment records for any company that had fewer
(count) then a specific number of payments but now need to pull them if
they
are missing a specific payment number.

We are tying to validate a large amount of data.

thanks

 

[lioneyes]

Ok, so I can't get the SELECT syntax to work right. I always have this
problem.

1) I assume I am using the select statements in the field row of a new
query design?

2) I can't even get a simple select statement to work. But when I tried to
do the subquery part of Klatuu solutions (the part that selects distinct for
payment 10) I got a "This query will only return one record" statement.
When I tried the whole select statement in his solution I kept getting syntax
error messages.

3) same problem when trying allen brownes method when making the first
select query statement got syntax error messages.

Even the syntax in the help file didn't work.

Can you guys write me the exact statement? Here are the actual table and
field names.

PROVNUMBER = company number, this is a text field because sometimes has
letter
PIPPAYNUM = payment number, this is a number field
2008PAYMENTS = this is the payment table name.

thanks.

 

[lioneyes]

Ok, I got it to work.

Thanks very much to both of you for your help.