Results 1 to 6 of 6
  1. #1
    hesykhia is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    4

    Is it possible to program a Union query to calc %'s within itself?

    Sorry, another question. I'm very new to access, and teaching myself as I go. I'm trying to manipulate SQL code into doing what I want without really understanding it completely, so I'm running into a lot of issues. Before you reply with "I don't understand why you're doing it this way, you should be doing it this way", please help me find out if there IS a way to do this in the manner I am approaching it, since this took a long time for my brain to figure out, but if there truly is a simpler, better way, please explain it in detail to me, otherwise it's just gonna confuse me a lot more.

    Before I post all my nonsense below, my main question is - is there a way to program a Union query via SQL to calculate percentages within itself? Like, the first query gives me a total, and the next query gives me new totals based on criterion, but needs an additional field to tell me what the % is compared to the first query.


    Okay, on to my nonsense that hopefully explains what I'm trying to do effectively -



    I need the report to look like this (I am not sure it will actually post in the format I'd like it to):



    Total Cases Disputed Dollar Total Percentage

    All Holds 11 $38,812.86


    All Approved 7 $27,131.32 % (of All Holds)

    Appr w/o Resub 5 $14,009.27 % (of All Appr)

    Appr w/ Resub 2 $13,122.05 % (of All Appr)


    All Declined 4 $11,681.54 % (of All Holds)

    Decl w/o Resub 3 $7,816.14 % (of All Decl)

    Decl w/ Resub 1 $3,865.40 % (of All Decl)




    I'm using a Union query to stick all the queries I need into one report.

    Here is the complete SQL code (sorry it's so long). Ignore the "select null" pieces, they are my bandaid to get the report to separate where I need it to. It just links to an empty table.

    Code:
    SELECT "All Holds" As Type, Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
     
    UNION ALL 
     
    SELECT null as Type, null as [CountOfDate email received], null as [SumOfDisputed Dollar Amount]
    from [extra] 
     
    UNION ALL
     
    SELECT "All Approved" As Type, Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
    GROUP BY [Holds Table].[SHU Decision]
    HAVING ((([Holds Table].[SHU Decision])="1"))
     
    UNION ALL
     
    SELECT"Approved without Resubmission" As type,  Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
    GROUP BY [Holds Table].[SHU Decision], [Holds Table].[Resubmitted?]
    HAVING ((([Holds Table].[SHU Decision])="1") AND (([Holds Table].[Resubmitted?])=False))
     
    UNION ALL 
     
    SELECT"Approved with Resubmission" As type,  Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
    GROUP BY [Holds Table].[SHU Decision], [Holds Table].[Resubmitted?]
    HAVING ((([Holds Table].[SHU Decision])="1") AND (([Holds Table].[Resubmitted?])=True))
     
    UNION ALL 
     
    Select null as Type, null as [CountOfDate email received], null as [SumOfDisputed Dollar Amount]
    from [extra] 
     
    UNION ALL 
     
    SELECT "All Declined" As Type, Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
    GROUP BY [Holds Table].[SHU Decision]
    HAVING ((([Holds Table].[SHU Decision])="2"))
     
    UNION ALL 
     
    SELECT "Declined without Resubmission" As type, Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
    GROUP BY [Holds Table].[SHU Decision], [Holds Table].[Resubmitted?]
    HAVING ((([Holds Table].[SHU Decision])="2") AND (([Holds Table].[Resubmitted?])=False))
     
    UNION ALL 
     
    SELECT "Declined with Resubmit" As type, Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
    GROUP BY [Holds Table].[SHU Decision], [Holds Table].[Resubmitted?]
    HAVING ((([Holds Table].[SHU Decision])="2") AND (([Holds Table].[Resubmitted?])=True));
    *phew* So, is this even possible or am I just screwed? I know exactly how to calculate all of this in Excel, within seconds, so I'm getting really frustrated that I can't do things that seem logical to me in Access without hours of scouring the internet for code I don't understand. That aside, I would appreciate any help you can give me, or even a "you ARE screwed, maybe try it this way". Much obliged!

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845

  3. #3
    hesykhia is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    4
    How is it a duplicate post if it's on a completely different forum? I posted a few places to reach a larger amount of people so I can get a helpful answer...

  4. #4
    hesykhia is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    4
    Apologies, I just read the link that was provided on that post. I didn't realize it was bad form to post multiple requests without referencing the other posts I've made. My bad!

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    In spite of what you said... there is a way, way, way easier way to do this. Calculate the results on a report, trying to do it in a query is much harder.

    The method you use depends on whether the fields you stipulated in this request are the ONLY things you are showing on the report or if you are showing detail related information as well.

    Assuming it is a summary report only and you are not showing any information other than the fields you listed.

    Your base query would be something like:

    Code:
    SELECT "All Declined" As Type, Count([Holds Table].[Date email received]) AS [CountOfDate email received], Sum([Holds Table].[Disputed Dollar Amount]) AS [SumOfDisputed Dollar Amount]
    FROM [Holds Table]
    WHERE ((([Holds Table].[Date email received]) Between [Enter start date:] And [Enter end date:]))
    assuming the name of this query to be qryBase

    On your report for your first item you would have:

    Total holds
    =dcount("*", "qryBase")
    =dsum("[SumofDisputed Dollar Amount]", "qryBase")

    All Approved
    =dcount("*", "qryBase", "[SHU Decision] = 1")
    =dsum("[SumofDisputed Dollar Amount]", "qryBase", "[SHU Decision] = 1")

    Approved w/out Resubmit
    =dcount("*", "qryBase", "[SHU Decision] = 1")
    =dsum("[SumofDisputed Dollar Amount]", "qryBase", "[SHU Decision] = 1 and [Resubmittal] <> -1")

    Approved w/ Resubmit
    =dcount("*", "qryBase", "[SHU Decision] = 1")
    =dsum("[SumofDisputed Dollar Amount]", "qryBase", "[SHU Decision] = 1 and [Resubmittal] = -1")

    and so on

    note you'd have to add the SHU decision and resubmittal field to the query to have this work.

  6. #6
    hesykhia is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    4
    Thank you, rpeare! I'll experiment with that and see if I can figure it all out. I appreciate the help!

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

Similar Threads

  1. Replies: 4
    Last Post: 12-20-2015, 02:35 PM
  2. Query iif calc too long
    By seocavaz in forum Queries
    Replies: 7
    Last Post: 07-07-2015, 05:39 PM
  3. Correlation calc in query
    By mike02 in forum Queries
    Replies: 2
    Last Post: 06-10-2013, 02:18 PM
  4. Iff query with date calc?
    By technet in forum Queries
    Replies: 3
    Last Post: 02-08-2012, 10:17 AM
  5. Program a 30-day trial into my Access Program?
    By genghiscomm in forum Programming
    Replies: 1
    Last Post: 05-26-2011, 02:14 PM

Tags for this Thread

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