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.