Unable to make GroupBy work as intended

[ultra vires]

Hey guys need your help here. I will go ahead and describe my problem and what i intend and what i am getting.

Platform for SQL Queries : MS Access

There are 2 identical table in terms of table structure, they contain information ( RAW Data) about month, money i spent in that month and for what thing i spent my money on.

Now what i want to do is compare these two tables and subtract total value spent.

1st table has data from year 2005
2nd table has data from year 2006

The response i am getting is actually adding "Total Value" multiple times. How do i go about this ?

SELECT s2.[Month],SUM(s2.[Total Value]), SUM(d.[Total Value]), s2.[Description_det] 
FROM  Sheet2 AS s2
INNER JOIN 
(
SELECT s1.[Month],SUM(s1.[Total Value]), s1.[Description_det] FROM  Sheet1 AS s1
GROUP BY s1.[Month],s1.[Description_det]
)d ON  s2.[Description_det]=d.[Description_det] AND s2.[Month] = d.[Month]
GROUP BY s2.[Month],s2.[Description_det]
;

[timothybard]

Can you tell me what the fields in each of the tables are and what the table d contains?

It seems like the issue you are having is that is that your join is creating an instance where each record for a particular month in your first table is being joined to each record for the same month in the second table. The easiest solution is to create three separate queries: the first will take the data in your first table, group by month and sum by total value; the second will do the same except for the second table; the third query will take both of those queries, join on month and then display the total value from each query. You can also perform the subtraction in the third query.

[ultra vires]

Yes sir that's exactly what i want to do.
Table Structure:
{
Id (number)
Month ( Text)
Total Value(Number)
Description_det(Tex)
}

Both the tables have this same structure

[timothybard]

Try creating the following three queries. If you use a name other than the ones I provide, then you will need to update the last query to refer to the names you used for the first two.

Query1:

SELECT Sheet1.Month, Sum(Sheet1.[Total Value]) AS [SumOfTotal Value]
FROM Sheet1
GROUP BY Sheet1.Month;

Query2:

SELECT Sheet2.Month, Sum(Sheet2.[Total Value]) AS [SumOfTotal Value]
FROM Sheet2
GROUP BY Sheet2.Month;

Query3:

SELECT Query1.Month, Query1.[SumOfTotal Value], Query2.[SumOfTotal Value], [Query1].[SumOfTotal Value]-[Query2].[SumOfTotal Value] AS Diff
FROM Query1 LEFT JOIN Query2 ON Query1.Month = Query2.Month;

[ultra vires]

Ten minutes after reading your 1st post i realized how simple it was and how stupid i was :)

Thanx for that suggestion and your 2nd post :)