Master Query SQL code help!

Please see my previous post here, to understand my current question a little more thoroughly: access-programmers.co.uk/forums/showthread.php?t=285364

I created 5 different queries with different outcomes of the same fields. I'm trying to make a report that includes all of these. I know I could make the report by doing subreports, but that would force the user running the report to enter the parameter data 5+ times, which would get quite onerous.

There has GOT to be a way to make a master query, that I could stick onto a report, and the user would only have to type the parameters in once.

Any ideas on how to go about coding this in SQL? I've been reading through how to do a union query, but I'm not sure if this is the best option or not, as I don't know how it would work with only populating the parameter fields once.


Here is the SQL from a couple of my queries:

"zHoldApprNoResubTotals" Query

SELECT 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));


"zHoldDeclResubTotals" Query

SELECT 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));

If you parameterize start and end date, why not parameterize the SHU Decision? Why have two exact same queries that differ only by that single digit?

You can open a form in the Report_Open() event, and get your user to the set the dates on that form, and then have all the queries read their parameters from that form.

Consider SQL like this . . .

SELECT Count(t.EmailDate) AS [Email Date Count], Sum(t.DisputedAmount) AS [Total Disputed]
FROM [Holds Table] As t
WHERE t.EmailDate Between Forms!fQueryData.StartDate AND Forms!fQueryData.EndDate
GROUP BY t.SHUDecision, t.IsResubmitted
HAVING t.SHUDecision = Forms!fQueryData.SHUDecision
AND t.IsResubmitted;

Click to expand...

. . . which can read the values from an open form, in this case a form name "fQueryData".

Thanks for the replies! When I put that SQL in, it asks for parameters for everything, which doesn't give me the info I need. I can't parameterize SHU Decision because I need to have each different situation (the 5 separate queries) end up on one big report, for the same date range. If I parameterize it, it only gives me what I put into it, but I need all the info in the same place. (Basically, it's telling me total volume, and then it is also telling me the # of accounts approved, # declined, resubmitted or not, and how much $ for each, all in one report) Does that make sense? Is this even possible?

I'm really just hoping to figure out a way to have the same date range populate 5 queries onto one report, without having to enter the range in multiple times. I need to make this as easy as possible to run, since I won't be the one running the reports.

hesykhia said:

When I put that SQL in, it asks for parameters for everything,

Click to expand...

Yes, the code presents you with an idea, not a turn-key solution. You need to implement it with the naming and objects that exist in your system.

You need to research the GROUP BY clause (http://www.w3schools.com/sql/sql_groupby.asp). I don't understand your data, so I will use a sample Sales table to show what I mean:

Sales
SalesPerson, SalesType, SalesAmount
Bob, Retail, 15
Sam, Wholesale, 120
Bob, Retail, 25
Bob, Wholesale, 150
Sam, Retail, 10

Then,
^gives total sales

If I start adding fields to the SELECT and GROUP BY clauses I can break that down as I see fit:

^totals by SalesPerson

^totals by SalesType

^totals by SalesPerson & SalesType


If you have a hard time envisioning the data that gets generated, make a table in Access with the actual data i posted, then try running the SQL I posted as well.

The answer to your issue is to appropriately use the SELECT/GROUP BY clauses of your SQL and not the HAVING clause.

MarkK said:

Yes, the code presents you with an idea, not a turn-key solution. You need to implement it with the naming and objects that exist in your system.

Click to expand...

Ah, I see. That makes more sense. Thanks, MarkK!

That is an amazing resource, plog, so thank you so much for that! I will be using it a TON while I try to figure out SQL.

I did find a way to make the report look like I wanted it to, through using a Union query. Here's a sample of the code I used:

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"))

Click to expand...

The "SELECT null" stuff is just putting a line break into the report utilizing a blank table. The full code is giving me the correct output, and I was able to format the report to look the way I wanted it to. Just wanted to let you guys know that I found a solution. Thank you both so much for your input, though! I appreciate it!