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

[hesykhia]

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.

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!

[CJ_London]

duplicate post

http://www.access-programmers.co.uk/...=1#post1479901

[hesykhia]

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...

[hesykhia]

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!

[rpeare]

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:

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.

[hesykhia]

Thank you, rpeare! I'll experiment with that and see if I can figure it all out. I appreciate the help!