Doing a select query from a count query

Question

So I am very new to SQL and Access. I have a payments table, an invoice table, and a bridge table between the two. I am trying to see which invoices use multiple payments to pay for them. I was able to use a count query to find which clients use multiple payments, and then query the count query to find which invoices are connected to which client, but I want to do that all in one query. Any recommendations? I have some sample code, but its basically just changing around the Access SQL code to try and get it to work, but I really don't know what I am doing. Thanks for any help! SELECT INVOICE.ID, PAYMENT.ID
FROM PAYMENT INNER JOIN (INVOICE INNER JOIN REDEMPTION ON INVOICE.ID = REDEMPTION.INVOICE_ID_NUMBER) ON PAYMENT.ID = REDEMPTION.PAYMENT_ID_NUMBER
WHERE ((Count([PAYMENT].[CLIENT_ID_NUMBER])>1));

Answer 1

Also trying, but not sure what I am doing wrong

SELECT INVOICE.ID

(SELECT PAYMENT.CLIENT_ID_NUMBER, Count(INVOICE.ID) AS CountOfID
FROM PAYMENT INNER JOIN (INVOICE INNER JOIN REDEMPTION ON INVOICE.ID = REDEMPTION.INVOICE_ID_NUMBER) ON PAYMENT.ID = REDEMPTION.PAYMENT_ID_NUMBER
GROUP BY PAYMENT.CLIENT_ID_NUMBER
HAVING (((Count(INVOICE.ID))>1))) AS PAYMENTS1,

FROM PAYMENT INNER JOIN ((INVOICE INNER JOIN PAYMENTS1 ON INVOICE.CLIENT_ID_NUMBER = PAYMENTS1.CLIENT_ID_NUMBER) INNER JOIN REDEMPTION ON INVOICE.ID = REDEMPTION.INVOICE_ID_NUMBER) ON PAYMENT.ID = REDEMPTION.PAYMENT_ID_NUMBER
WHERE (((INVOICE.CLIENT_ID_NUMBER)=[PAYMENTS1].[CLIENT_ID_NUMBER]));

Answer 2

I think you might be aiming at something like this:

SELECT Payment.ClientID, Redemption.Invoice_ID_Number,
COUNT(*) AS NumberOfPayments
FROM Payment INNER JOIN Redemption
ON Payment.ID = Redemption.Payment_ID_Number
GROUP BY Payment.ClientID, Redemption.Invoice_ID_Number
HAVING COUNT(*) >1;

As the Redemption table includes an Invoice_ID_Number column, there is no need to include the Invoice table in the query, unless you need to return values from other candidate key columns in The Invoice table.

As you are new to Access, you might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes, amongst other things, examples of queries which go slightly beyond simple joins.

---

Ken Sheridan, Stafford, England

Answer 3

Hi Jdw0024,

Is your issue solved?

I find that you did not follow up this thread after posting the issue.

If your issue is solved then I suggest you to post your solution and mark it as an answer.

If your issue is still exist then try to refer the solution given by the community members.

If then also you have any further questions then let us know about it.

We will try to provide further suggestions to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].