 |  |  |  |  |  |  |  |
Unable to make GroupBy work as intended
Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
Thread Solved |
•
•
Join Date: Feb 2006
Posts: 50
Reputation: 
Solved Threads: 5
Junior Poster in Training
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 ?
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] ;
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.
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.
•
•
Join Date: Feb 2006
Posts: 50
Reputation:
Solved Threads: 5
Junior Poster in Training
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
Table Structure:
{
Id (number)
Month ( Text)
Total Value(Number)
Description_det(Tex)
}
Both the tables have this same structure
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:
Query2:
Query3:
Query1:
sql Syntax (Toggle Plain Text)
Query2:
sql Syntax (Toggle Plain Text)
Query3:
sql Syntax (Toggle Plain Text)
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.
 |
Similar Threads
- Alternatives to winsound? (Python)
- Make program work on other PCs (VB.NET)
- How do i make my program work for 5 times and then Expire (VB.NET)
- need help to make this program work (C++)
- how to make clrscr() work (C++)
- Explorer Blocking Script (JavaScript / DHTML / AJAX)
- which language ? (Computer Science)
- Please help me make this script work in Netscape (JavaScript / DHTML / AJAX)
- pyapal - i am unable to make payment through it - urgent help needed (eCommerce)
Other Threads in the MS Access and FileMaker Pro Forum
- Previous Thread: Help with ms access
- Next Thread: Hi need your help
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Latest MS Access and FileMaker Pro Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
