Help with simple query to produce sum

[afs84]

Hello,


I'm currently having an issue where I have hit a brick wall so any help would be appreciated.

So lets say I have two tables, DailyIncome and DailyExpense with no relationships, for simplicity lets assume both only have two fields... DailyIncome has Month and IncomeAmount, and Daily Expense has Month and ExpenseAmount.

I'm trying to build a query to show me sums of IncomeAmount and ExpenseAmount from the two tables for a certain month. simplicity lets assume the following records:

DailyIncome Table:
Month.........IncomeAmount
January............100
January............200

DailyExpense Table:
Month.........Expense Amount
January............200
January............300

What I want the query to show me is the following Sums based on the criteria "January":

Month.......IncomeAmount......ExpenseAmount
January.............300.......................500

I did a simple select query, but for some reason i keep getting either wrong calculation (doubled)or extra records which aren't there added.

Usually this happens:

Month.......IncomeAmount......ExpenseAmount
January............600......................1000

I removed the sums and usually see the following:

Month........IncomeAmount......ExpenseAmount
January............100........................200
January............200........................200
January............100........................300
January............200........................300

It seems like its populating empty values with repeated data.

My most basic code looks like this:

SELECT DailyIncome.Month, DailyIncome.IncomeAmount, DailyExpense.ExpenseAmount,
FROM DailyIncome, DailyExpense
WHERE (((DailyIncome.Month)="January"));



I tried playing around with the code, choosing the month field from both tables, selecting unique records but nothing seems to work...Im pretty sure by now that i'm doing something really wrong or that i don't really understand queries... Please help and bare with me because as i said, my understanding of queries is very shallow.

 

[Ranman256]

Join the 2 tables on month then sum the money.

 

[afs84]

Tried that...still giving randomly different but similar results

 

[plog]

So I have two tables, DailyIncome and DailyExpense with no relationships

That is incorrect. They are related via Month. So what you need to do is Sum the money for each table seperately, then JOIN those 2 queries on the Month field. Also, don't use the term 'Month' for a field name--its a reserved word. Instead prefix it with what that Month is for (IncomeMonth, ExpenseMonth, etc.),

 

[afs84]

The "Month" field name is just an example for simplicity.

Your solution is good but here's the issue. If I do create separate queries for each table and then join both queries on the Month field, I will get correct results if and only if - back to my example "January" Exists in both tables. Suppose there were no expense records for "January", then i'll run into trouble with the direction of the join and no results will be displayed. I want to be able to query both tables and display all results even if there was no enter for such criteria - e.g zero -. Am I making sense or am I completely missing something here?

 

[plog]

You would then need a 3rd source which lists all the months you want to report on. Do you have such a list? If so, you then make a query off of it and then LEFT JOIN those 2 subqueries I mentioned to it. Then for the values you would use the NZ function (http://www.techonthenet.com/access/functions/advanced/nz.php) to force the nulls to 0.

 

[afs84]

Mmmm....ok

Could you please just elaborate some more with the example I gave above?

 

[plog]

Yes, you would need a list of months you want to report on. That could be a query where you get all the unique months or a table you populate. Do you have a table with all the months you want to report on in it?

 

[afs84]

No I don't have a table for months. I did create one though and connected it to the previous two queries, then queried it, and magically it was able to reproduce my original problem but now with the sums produced by the two other queries.

Should it be that hard?

 

[plog]

If there was no pain in learning, we wouldn't care to understand the how's and why's so we could avoid it in the future. We are getting there though.

So what's your SQL for your 2 sub-queries and your main query?

 

[afs84]

Now my main issue is this, the query seems to populate null values with previous values. So from my example above:

Month........IncomeAmount......ExpenseAmount
January............100........................200
January............200........................200
January............100........................300
January............200........................300

Where it should be:

Month........IncomeAmount......ExpenseAmount
January..............0.......................200
January............100........................0
January.............0........................300
January............200.......................0

Could I use the NZ function you mentioned to force that?

 

[plog]

You're losing me. I see no Null values in the data you just posted. NZ can convert null values to anything you want, but it can't convert any other value into something else.

 

[afs84]

Alright i'll explain my last post later...

So here is my code, had to edit it a bit to fit my example:

Query1:
SELECT DailyExpense.Month, Sum(DailyExpense.ExpenseAmount) AS SumOfExpenseAmount
FROM DailyExpense
GROUP BY DailyExpense.Month;

Query2:
SELECT DailyIncome.Month, Sum(DailyIncome.IncomeAmount) AS SumOfIncomeAmount
FROM DailyIncome
GROUP BY DailyIncome.Month;

Main Query:

SELECT Query1.Month, Query1.SumOfExpenseAmount, Query2.SumOfIncomeAmount
FROM Query1 LEFT JOIN Query2 ON Query1.Month = Query2.Month
WHERE (((Query1.Month)="January"));

 

[afs84]

In my last post i was talking about my original query where i queried 2 tables at the same time and was getting wrong numbers.

I meant the query should in theory return this:

Month........IncomeAmount......ExpenseAmount
January............null.......................200
January............100.......................null
January............null........................300
January............200.......................null

So I was asking why was access repeating values instead of simply reporting null or zero? and was it possible to use the NZ function to fix that.

 

[plog]

Your main query needs to be based off of your Months table. Let's call it ReportMonths:

SELECT ReportMonths.MonthName, Query1.SumOfExpenseAmount, Query2.SumOfIncomeAmount
FROM ReportMonths
LEFT JOIN Query1 ON ReportMonths.MonthName=Query1.Month
LEFT JOIN Query2 ON ReportMonths.MonthName=Query2.Month

You might have to use NZ around your SumOf fields.

 

[afs84]

It now gives a syntax error, tried to make sure nothing is incorrect and nothing is. Missing Operator.

 

[afs84]

This seems to work:

SELECT Query1.Month, Query1.SumOfExpenseAmount, Query2.SumOfIncomeAmount
FROM (ReportMonths INNER JOIN Query1 ON ReportMonths.MonthName = Query1.Month) INNER JOIN Query2 ON ReportMonths.MonthName = Query2.Month;

 

[plog]

INNER JOIN isn't going to cut it. If no date is in your subqueries for a month that month will not show up.

 

[afs84]

Although still, If one table doesn't contain an entry for "January" for example, nothing is returned
** Edit: Just saw your post, you are right...So what now?

 

[plog]

use LEFT JOIN