Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show only one to many matches, not one to one.

Status
Not open for further replies.

KashMarsh

Technical User
Jun 6, 2007
13
US
I have a table with invoices. I have another table with payments for those invoices. They join by a common field called [InvoiceKey]. I want my query to ONLY show the invoices that have multiple payments not the ones that have only one payment. Is this possible? If so, how do I do this?

Thanks in advance.
 
something like this (SQL code)
Code:
SELECT *
FROM (tblInvoices I
INNER JOIN tblPayments P ON I.InvoiceKey = P.InvoiceKey)
INNER JOIN (
  SELECT InvoiceKey FROM tblPayments GROUP BY InvoiceKey HAVING Count(*)>1
) M ON I.InvoiceKey = M.InvoiceKey

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks for the reply! I tried the query, however it did not produce the results as expected. It came up with the invoice information showing up over and over on the screen and then froze. I will submit a screen print of it when my application comes back.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top