Help Needed: Aggregating Multiple Columns in MS Access Query

[Jinx]

I have a table [AAAM] of 15 columns as specified below:

AA100200:Project Code
AAAM140:Staff Code (Individuals' ID #)
AAAM010: Year

Then 12 Columns for each month starting AAAM020 to AAAM130 where the time spent by each individual on the given project in a particular year is entered. I want to aggregate time in all the columns from AAAM020 to AAAM130 grouped by Project Code, Year and Staff Code .

With this (http://www.thescripts.com/forum/thread534172.html) thread in mind , I made two separate queries.

Qury 1 on the Table Named AAAM:
-------------------------------
SELECT dbo_AAAM.AA100200, dbo_AAAM.AAAM010, dbo_AAAM.AAAM140, Sum(dbo_AAAM.AAAM020) AS SumOfAAAM020, Sum(dbo_AAAM.AAAM030) AS SumOfAAAM030, Sum(dbo_AAAM.AAAM040) AS SumOfAAAM040, Sum(dbo_AAAM.AAAM050) AS SumOfAAAM050, Sum(dbo_AAAM.AAAM060) AS SumOfAAAM060, Sum(dbo_AAAM.AAAM070) AS SumOfAAAM070, Sum(dbo_AAAM.AAAM080) AS SumOfAAAM080, Sum(dbo_AAAM.AAAM090) AS SumOfAAAM090, Sum(dbo_AAAM.AAAM100) AS SumOfAAAM100, Sum(dbo_AAAM.AAAM110) AS SumOfAAAM110, Sum(dbo_AAAM.AAAM120) AS SumOfAAAM120, Sum(dbo_AAAM.AAAM130) AS SumOfAAAM130
FROM dbo_AAAM
GROUP BY dbo_AAAM.AA100200, dbo_AAAM.AAAM010, dbo_AAAM.AAAM140;
------------------------------------

Then based upon Query 1, I am running this query:

SELECT Query1.AA100200, Query1.AAAM010, Query1.AAAM140, Sum([SumOfAAAM020]+[SumOfAAAM030]+[SumofAAAM040]+[SumOfAAAM050]+[SumOfAAAM060]+[SumOfAAAM070]+[SumOfAAAM080]+[SumOfAAAM090]+[SumOfAAAM100]+[SumOfAAAM110]+[SumOfAAAM120]+[SumOfAAAM130]) AS Expr1
FROM Query1
GROUP BY Query1.AA100200, Query1.AAAM010, Query1.AAAM140;

But there are no results. Can anyone please guide me as to where is the mistake.

Please help. Thanks.
Jinx

[jimatqsi]

Hi Jinx,
Tell us, does the query1 give any result? Is it only after you run the second query that the results disappear?

What I do whenever I get this sort of thing I start a process of elimination. First I copy by query to a temporary name. Then I start eliminating criteria one at a time until I get some results. Then I look more carefully at the criteria and at the data being tested in that criteria.

If that doesn't lead me to the problem, then I look at my JOIN statements. I'll eliminate one table or another from the JOINs to see what data is delivered under each circumstance. Or I will change the details of the join, change from RIGHT to LEFT, that sort of thing, to see the difference in the results.

Eventually I see and understand the problem.

Jim

[Jinx]

Thanks Jim.

This is what I did. Created a new database with only one table and 15 Columns. Again created two queries and started entering dummy data. And this is my observation:

"With any cell in a column having a 'NULL' value, if you apply the aggregate function of SUM() with two or more columns as the function arguments in a Total Query, Access query simply returns no values. There should either be a zero or some value in each and every cell of the columns of data you are trying to process.

Is it a bug or a deficiency in Access 2003. I don't know.

Anyway, thanks a lot for your time and guidance.
I really appreciate this.

Jinx

[jimatqsi]

Hi Jinx (I love saying that :)),
Sounds like you need to test for nulls and turn the nulls into zeros before summing. Instead of simplying references the field that comes out of the first query you need to reference it like this:

SomeName:IIF (IsNull([thefieldname]),0,[thefieldname])

That will return a 0 in SomeName if you have a null, and the value of SomeName if you don't.

Crosstab queries are famous for posing this problem. It got me when I was a little newer than I am now (I'm still kinda new to Access/VB).

Jim

Thanks Jim.

This is what I did. Created a new database with only one table and 15 Columns. Again created two queries and started entering dummy data. And this is my observation:

"With any cell in a column having a 'NULL' value, if you apply the aggregate function of SUM() with two or more columns as the function arguments in a Total Query, Access query simply returns no values. There should either be a zero or some value in each and every cell of the columns of data you are trying to process.

Is it a bug or a deficiency in Access 2003. I don't know.

Anyway, thanks a lot for your time and guidance.
I really appreciate this.

Jinx

[Jinx]

Thank You Jim....Yes this is what I need to do. I have a lot of NULLs in the table and of course I don't want to replace them with ZEROs.

Thanks for the help....I really appreciate this.

J..