Multiple Criteria

[bdjb]

Hello,
I have an Access find duplicates query, that part works fine. It pulls in invoices that can be entered multiple times... once with Processing, Payment Processing or Paid as a status. How can I set the query to only pull in Duplicates for the Invoice field, and those that also have paid or the other two status? If I add Paid as a Criteria I only get the Paid ones etc.

What I'm looking to do is pull in all multiples for the Invoice Number field, but only those that also have a status of Paid so I can delete the Processed ones (So I need to see both entries)

Thanks in advance

 

[SkipVought]

How about posting your query?

 

[bdjb]

It's just a standard Find Duplicates query at this point, which does pull in the data wanted, but then some, as invoices can have several entries of pending, and none with paid. So I just want to see those that have multiple entries, and where at least one of them are paid for the status

SELECT [MM Pacific Legal Invoices].[Invoice Number], [MM Pacific Legal Invoices].[Invoice Status], [MM Pacific Legal Invoices].[LOB], [MM Pacific Legal Invoices].[Client/ Matter Name], [MM Pacific Legal Invoices].[Law Firm/Attorney], [MM Pacific Legal Invoices].[LOB Contact], [MM Pacific Legal Invoices].[Cost Center Detail], [MM Pacific Legal Invoices].[Invoice Status Date], [MM Pacific Legal Invoices].[Invoice Amount], [MM Pacific Legal Invoices].[Invoice Dispensation]
FROM [MM Pacific Legal Invoices]
WHERE ((([MM Pacific Legal Invoices].[Invoice Number]) In (SELECT [Invoice Number] FROM [MM Pacific Legal Invoices] As Tmp GROUP BY [Invoice Number] HAVING Count(*)>1 )))
ORDER BY [MM Pacific Legal Invoices].[Invoice Number];

Thanks

 

[Andrzejek]

Your statement would be a lot easier to read this way:

[pre]
SELECT [Invoice Number], [Invoice Status], [LOB],
[Client/ Matter Name], [Law Firm/Attorney],
[LOB Contact], [Cost Center Detail],
[Invoice Status Date], [Invoice Amount], [Invoice Dispensation]
FROM [MM Pacific Legal Invoices]
WHERE ((([Invoice Number]) In
(SELECT [Invoice Number]
FROM [MM Pacific Legal Invoices] As Tmp
GROUP BY [Invoice Number] HAVING Count(*)>1 )))
ORDER BY [Invoice Number];
[/pre]

BTW - What a terrible way to name fields in a table:[tt] [Client/ Matter Name][/tt] and [tt][Law Firm/Attorney][/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.