[RESOLVED] Compare summed values from two tables

[tim1969]

Hi,

I am working in Access 2007 and have a database with two tables. One tblEnergy contains energy consumption data (date, energy) the other contains production data (date, production). (The actual field names are different).

There may be several data points per month for each table, e.g.

Date Energy
1 Jan 08 500
3 Jan 08 200
21 Jan 08 100
8 Feb 08 150

Date Production
2 Jan 08 5000
5 Jan 08 8000
23 Feb 08 1500

What I want to do is to write a query that gives me for each month, the total energy use and total production, i.e.

Month Energy Production
Jan 08 800 13000
Feb 08 150 1500

Ultimately I also want to write a second query that gives me the Energy/Production quotient instead of the two separate fields.

I have worked out that I can write two queries each using SQL like this:

SELECT FORMAT(Date,"yyyy mm") as InvMth, SUM(Energy) as TotNRG FROM tblEnergy GROUP BY FORMAT(Date,"yyyy mm")

and a similar one for the production table. If I save them, I can then use the QBE grid to write a third query that refers to these two queries, joining on month (i.e. format(date,"yyyy mm")).

This gives me the outcome that I'm looking for, but I'd like to get the whole thing into a single SQL statement if I could - this is where I am totally stuck.

Do I need to use subqueries? These do my head in for some reason...

Any help appreciated.

[CVMichael]

Just join the 2 queries by month

Something like:

SELECT NRG.*, PROD.TotProd
FROM (
    SELECT FORMAT(Date,"yyyy mm") as InvMth
        , SUM(Energy) as TotNRG
    FROM tblEnergy
    GROUP BY FORMAT(Date,"yyyy mm")
) AS NRG
INNER JOIN (
    SELECT FORMAT(Date,"yyyy mm") as InvMth
        , SUM(Production) as TotProd
    FROM tblProduction
    GROUP BY FORMAT(Date,"yyyy mm")
) AS PROD ON NRG.InvMth= PROD.InvMth

This will work only if both tables have records in the same months. You can get around that, but you have to make a third sub query for that. Let me know if you need that.

[tim1969]

Thanks CVMichael, that did the trick.

I had the concept but just couldn't work out the syntax. Sometimes I think SQL is the least structured language I've come across!

For the record, I modified it slightly to show invoice months with no production as follows (some of the field names are a bit different):

SELECT NRG.InvMth, NRG.TotNRG, Prod.TotProd
FROM
(SELECT FORMAT(dtmDateTime,"yyyy mm") AS InvMth, SUM(Energy) AS TotNRG FROM tblInvoices GROUP BY FORMAT(dtmDateTime,"yyyy mm")) AS NRG
LEFT JOIN
(SELECT FORMAT(dtmProdDate,"yyyy mm") AS ProdMth, SUM(sngQty) AS TotProd FROM tblProduction GROUP BY FORMAT(dtmProdDate,"yyyy mm")) AS Prod
ON NRG.InvMth=Prod.ProdMth