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

H

hesykhia

New member
Local time
Yesterday, 23:34
Joined
Mar 15, 2016
Messages
8
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 (edit: sorry, it doesn't look like it did when I wrote the post):

Total CasesDisputed Dollar TotalPercentage
All Holds 11$38,812.86

All Approved 7$27,131.32% (of All Holds)
Appr w/o Resb 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.

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));
Click to expand...
*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!
 
Actually, before you go to far, you need to clean up that SQL. That can be accomplished with one SELECT clause instead of that hacked together UNION monstrosity.

Like I tried to indicate in the last related post (http://www.access-programmers.co.uk/forums/showthread.php?t=285366), you need to move your HAVING criteria to the SELECT/GROUP BY clauses. You need a new calculated field to determine each records type, then you GROUP BY each type instead of using duct tape to merge them all together.
 
Sorry plog, I didn't change it before because it was working and I didn't want to mess it up further. But I will do a bit more experimenting with it and apply your suggestion, unless I end up doing something completely different.

I didn't realize it was bad form to double post in other forums, my apologies. I just figured it was a way to get more advice quickly. I did receive a response on utteraccess.com/forum/index.php?showtopic=2035668 that looks promising, so I will try to see if it can be applied to what I'm trying to do and report back.

Thank you guys all the same!
 
You must log in or register to reply here.

Similar threads

O
Is it possible to create a query of queries without using UNION
2
Replies
24
Views
1,114
Pat Hartman
P
essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
626
essaytee
essaytee
K
Query to to exclude records with a date within last 4 months, and show records where most recent date is older than that
Replies
9
Views
786
kevin_maher
K
rth@trinitylabs.org
Issues attempting to open a form to a specific record
Replies
6
Views
649
rth@trinitylabs.org
rth@trinitylabs.org
A
Is It Possible to Appear Desired Text At Next Line In A Text Box In Report?
Replies
6
Views
969
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom