Results 1 to 9 of 9
  1. #1
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338

    Combine three queries into one

    Hello

    How can i combine these three queries??

    SELECT Sum(T_MonthlyTotals2.Cash) AS [Cash Outstanding], T_MonthlyTotals.[Date of Posting From], T_MonthlyTotals.[Provider Name], T_MonthlyTotals2.[RCPT #], T_MonthlyTotals2.[Source of Payment], T_MonthlyTotals2.[Cash Cleared]


    FROM T_MonthlyTotals INNER JOIN T_MonthlyTotals2 ON T_MonthlyTotals.ID = T_MonthlyTotals2.[ID Main Table]
    GROUP BY T_MonthlyTotals.[Date of Posting From], T_MonthlyTotals.[Provider Name], T_MonthlyTotals2.[RCPT #], T_MonthlyTotals2.[Source of Payment], T_MonthlyTotals2.[Cash Cleared]
    HAVING (((T_MonthlyTotals2.[Cash Cleared])=No));


    SELECT T_MonthlyTotals2.Check AS [Check Outstanding], T_MonthlyTotals.[Date of Posting From], T_MonthlyTotals.[Date of Posting To], T_MonthlyTotals.[Provider Name], T_MonthlyTotals2.[RCPT #], T_MonthlyTotals2.[Source of Payment]
    FROM T_MonthlyTotals INNER JOIN T_MonthlyTotals2 ON T_MonthlyTotals.ID = T_MonthlyTotals2.[ID Main Table]
    WHERE (((T_MonthlyTotals2.[Check Cleared])=No));

    SELECT T_MonthlyTotals2.[Credit Card] AS [Credit Card Outstanding], T_MonthlyTotals.[Date of Posting From], T_MonthlyTotals.[Date of Posting To], T_MonthlyTotals.[Provider Name], T_MonthlyTotals2.[RCPT #], T_MonthlyTotals2.[Source of Payment]
    FROM T_MonthlyTotals INNER JOIN T_MonthlyTotals2 ON T_MonthlyTotals.ID = T_MonthlyTotals2.[ID Main Table]
    GROUP BY T_MonthlyTotals2.[Credit Card], T_MonthlyTotals.[Date of Posting From], T_MonthlyTotals.[Date of Posting To], T_MonthlyTotals.[Provider Name], T_MonthlyTotals2.[RCPT #], T_MonthlyTotals2.[Source of Payment], T_MonthlyTotals2.[Credit Card Cleared]
    HAVING (((T_MonthlyTotals2.[Credit Card Cleared])=No));

    Thank you

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How do you want them combined? Do you want UNION or JOIN?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Not sure i would like to get the sum by provider and month

  4. #4
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    I would like to join them

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't really join those three queries because they won't all three have the same providers and month/year because they are filtered.

    Also, if you want to summarize by month/year, need to extract those date parts from a date value (DateOReceipt ?)

    Can cash ever not clear?

    See if this gets you what you want.

    SELECT T_MonthlyTotals.ProviderID, Format([DateOfReceipt],"mmyy") AS MoYr, Sum(T_MonthlyTotals2.Cash) AS SumOfCash, Sum(T_MonthlyTotals2.Check) AS SumOfCheck, Sum(T_MonthlyTotals2.CreditCard) AS SumOfCreditCard
    FROM T_MonthlyTotals INNER JOIN T_MonthlyTotals2 ON T_MonthlyTotals.ID = T_MonthlyTotals2.MainTableID
    WHERE (((T_MonthlyTotals2.CashCleared)=No)) OR (((T_MonthlyTotals2.CheckCleared)=No)) OR (((T_MonthlyTotals2.CreditCardCleared)=No))
    GROUP BY T_MonthlyTotals.ProviderID, Format([DateOfReceipt],"mmyy");
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Yes cash can not clear sometimes. Same for check and credit card. I would need for it to gave me totals for the one's that didn't clear.

    Thank you

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try this instead:

    SELECT T_MonthlyTotals.ProviderID, Format([DateOfReceipt],"mmyy") AS MoYr, Sum(IIf([CashCleared]=False,[Cash],0)) AS CashSum, Sum(IIf([CheckCleared]=False,[Check],0)) AS CheckSum, Sum(IIf([CreditCardCleared]=False,[CreditCard],0)) AS CreditCardSum
    FROM T_MonthlyTotals INNER JOIN T_MonthlyTotals2 ON T_MonthlyTotals.ID = T_MonthlyTotals2.MainTableID
    WHERE (((T_MonthlyTotals2.CashCleared)=No) AND ((T_MonthlyTotals2.Cash)>0)) OR (((T_MonthlyTotals2.CheckCleared)=No) AND ((T_MonthlyTotals2.Check)>0)) OR (((T_MonthlyTotals2.CreditCardCleared)=No) AND ((T_MonthlyTotals2.CreditCard)>0))
    GROUP BY T_MonthlyTotals.ProviderID, Format([DateOfReceipt],"mmyy");
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Thank You very much that worked great. Just one more thing I would like to add one more column Grand Total; this would give me the sum off Cash,Check and Credit Card.

    1 0112 55 0 0 55
    2 0212 0 200 300 500


    Thank you

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SELECT T_MonthlyTotals.ProviderID, Format([DateOfReceipt],"mmyy") AS MoYr, Sum(IIf([CashCleared]=False,[Cash],0)) AS CashSum, Sum(IIf([CheckCleared]=False,[Check],0)) AS CheckSum, Sum(IIf([CreditCardCleared]=False,[CreditCard],0)) AS CreditCardSum, Sum(IIf([CashCleared]=False,[Cash],0)+IIf([CheckCleared]=False,[Check],0)+IIf([CreditCardCleared]=False,[CreditCard],0)) AS GrandSum
    FROM T_MonthlyTotals INNER JOIN T_MonthlyTotals2 ON T_MonthlyTotals.ID = T_MonthlyTotals2.MainTableID
    WHERE (((T_MonthlyTotals2.CashCleared)=No) AND ((T_MonthlyTotals2.Cash)>0)) OR (((T_MonthlyTotals2.CheckCleared)=No) AND ((T_MonthlyTotals2.Check)>0)) OR (((T_MonthlyTotals2.CreditCardCleared)=No) AND ((T_MonthlyTotals2.CreditCard)>0))
    GROUP BY T_MonthlyTotals.ProviderID, Format([DateOfReceipt],"mmyy");
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Can we combine 3 queries on 1 report? :?
    By radicrains in forum Queries
    Replies: 3
    Last Post: 10-28-2010, 09:35 PM
  2. Combine Queries
    By Steven.Allman in forum Queries
    Replies: 9
    Last Post: 08-30-2010, 12:13 PM
  3. Combine 3 Queries
    By Logix in forum Queries
    Replies: 1
    Last Post: 06-10-2010, 06:03 PM
  4. Combine crosstab queries
    By thart21 in forum Queries
    Replies: 3
    Last Post: 05-03-2010, 10:36 AM
  5. Combine queries
    By thart21 in forum Queries
    Replies: 1
    Last Post: 03-05-2010, 01:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums