none
Doing a select query from a count query RRS feed

  • 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));

    • Edited by Jdw0024 Saturday, April 14, 2018 7:53 PM
    Saturday, April 14, 2018 7:26 PM

All replies

  • 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]));

    Saturday, April 14, 2018 9:06 PM
  • 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

    Saturday, April 14, 2018 10:14 PM
  • 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 MSDNFSF@microsoft.com.

    Friday, April 27, 2018 9:41 AM
    Moderator