How to create query based on two query with different parameter

[azzurri]

I had created a database for electricity bill for 400 plants maintained by my company. One of its feature is that it can generate report for average consumption (Average Consumption Report)for past bills for a period of months-that is to be the parameter of the underlying query for this report- ie for average for past 6 months, the parameter will prompt for 'start month' and 'end month'. So far this had been really helpful in quickly generating an average consumption data for all 400 plants in one report summary.

However, in applying the past average consumption data for the purpose of comparison against current bill(latest bill) to detect any abnormality in consumption trend based on established variance/benchmark percentage, this had to be done sort of manually. Firstly, I generate the summary report for all the latest bill (current bill)received for those 400 plants (Bill Received for The month Report), then export this to Excel sheet, then I generate the Average Consumption Report for immediate preceding bills and then export this to Word than from Word copy and paste to Excel since exporting directly to Excel from the report will create format problem. Both report are based on two different query but from the same table source.

So I have two Excel sheet open and tiled vertically for easy matching of the current bill for each plant to its corresponding average in another sheet and apply some formula in the Excel to calculate the variance between the current bill and its average consumption.

Its OK since it still quite effective however I am toying with
idea on how all this can be represented by one report that can show all this info, one report that can show the variance between the average consumption and current bill consumption.
This sure will subtantially cut short my current way of doing thing.

Hope anyone can come up with the suggestion, I think this is a bit on Query issue.

TQ

 

[raskew]

Here's an idea, based on the Northwind database, which you might try and see if would be adaptable to your situation.

It consists of three queries, each of which you can copy/paste into Northwind.

(1) Query38 returns monthly purchase totals for each customer.

(2) Query39 incorporates the previous query and prompts for the billing month/year. It returns the average monthly purchase totals for months prior to the mm/yyyy you input.

(3) Query40 incorporates the previous two queries and prompts for a 'cap'. It returns records only for the companies whose current billing meets or exceeds the cap. For example, if you enter 1.25, it returns records where current billing >= 125% of previous average.


Query38

SELECT Orders.CustomerID, Format([OrderDate],"mm/yyyy") AS [mm/yyyy], Sum([unitprice]*[quantity]) AS MonTot
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, Format([OrderDate],"mm/yyyy")
ORDER BY Orders.CustomerID, Format([OrderDate],"mm/yyyy");

Query39

SELECT Query38.CustomerID, Avg(Query38.MonTot) AS AvgOfMonTot
FROM Query38
WHERE (((Query38.[mm/yyyy])<[enter mm/yyyy]))
GROUP BY Query38.CustomerID;

Query40

PARAMETERS [enter cap] Currency, [enter mm/yyyy] Text;
SELECT Query38.CustomerID, Query38.[mm/yyyy], Query38.MonTot, Query39.AvgOfMonTot, [montot]/[AvgOfMonTot] AS PerOfAvg
FROM Query38 LEFT JOIN Query39 ON Query38.CustomerID = Query39.CustomerID
WHERE (((Query38.[mm/yyyy])=[enter mm/yyyy]) AND (([montot]/[AvgOfMonTot])>[enter cap]))
ORDER BY [montot]/[AvgOfMonTot] DESC;