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!