Ad:

MS Access and FileMaker Pro Discussion Thread
Marked Solved
Views: 944

Aug 18th, 2009

Unable to make GroupBy work as intended

Expand Post »

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 ?

(Toggle Plain Text)

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]
;

Similar Threads

which language ? in Computer Science
Alternatives to winsound? in Python
Make program work on other PCs in VB.NET
How do i make my program work for 5 times and then Expire in VB.NET
need help to make this program work in C++

ultra vires

Reputation Points: 10

Solved Threads: 5

Junior Poster in Training

Offline

50 posts
since Feb 2006

Permalink

Aug 18th, 2009

Re: Unable to make GroupBy work as intended

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.

timothybard

Reputation Points: 27

Solved Threads: 29

Posting Whiz

Offline

317 posts
since Mar 2007

Permalink

Aug 18th, 2009

Re: Unable to make GroupBy work as intended

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

ultra vires

Reputation Points: 10

Solved Threads: 5

Junior Poster in Training

Offline

50 posts
since Feb 2006

Permalink

Aug 18th, 2009

Re: Unable to make GroupBy work as intended

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:

sql Syntax (Toggle Plain Text)
SELECT Sheet1.MONTH, Sum(Sheet1.[Total Value]) AS [SumOfTotal Value]
FROM Sheet1
GROUP BY Sheet1.MONTH;
SELECT Sheet1.Month, Sum(Sheet1.[Total Value]) AS [SumOfTotal Value]
FROM Sheet1
GROUP BY Sheet1.Month;

Query2:

sql Syntax (Toggle Plain Text)
SELECT Sheet2.MONTH, Sum(Sheet2.[Total Value]) AS [SumOfTotal Value]
FROM Sheet2
GROUP BY Sheet2.MONTH;
SELECT Sheet2.Month, Sum(Sheet2.[Total Value]) AS [SumOfTotal Value]
FROM Sheet2
GROUP BY Sheet2.Month;

Query3:

sql Syntax (Toggle Plain Text)
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;
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;

Last edited by peter_budo; Aug 18th, 2009 at 4:48 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

timothybard

Reputation Points: 27

Solved Threads: 29

Posting Whiz

Offline

317 posts
since Mar 2007

Permalink

Aug 21st, 2009

Re: Unable to make GroupBy work as intended

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

ultra vires

Reputation Points: 10

Solved Threads: 5

Junior Poster in Training

Offline

50 posts
since Feb 2006

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS Access and FileMaker Pro Forum Timeline: Help with ms access

Next Thread in MS Access and FileMaker Pro Forum Timeline: Hi need your help

DaniWeb Message

Cancel Changes

User Name
Password