Aggregate SUM on two different fields- IS there a better way? 1

[elinorigb]

Hi,

I'm trying to combine two separate fields into one SUM. Right now I'm taking one approach that works, though it seems to involve more steps than necessary.

I'll call the fields Data1 and Data2. I have individual queries that count all of the instances of Data1 and Data2. Once the instances are counted, a second query calculates the SUM for each field respectively.

Right now, I can't really combine the two SUMs into one SUM. This is the current query I'm using, but all it really does is display the two individual sums next to each other. I still have to do the calculation manually:

SELECT qryData1.SumOfData1, Sum(qryData2.Data2) AS SumOfData2
FROM qryData2, qryData2
GROUP BY qryData1.SumOfData1;

Thanks as always!

 

[lespaul]

are the two fields in the same table? Are you summing the fields the first time or counting? What is the structure of the first two queries?

Leslie

 

[elinorigb]

Its taken me awhile to respond. Sorry about that. The fields are in the same table. In the first queries, I have to count the data, since it is based on non-numeric entries. Then I'm able to SUM the counted data. I'll include the other queries below. There are quite a few of them, which is why I asked this in the first place.

Here are the two queries involving the first field:

qryBook2:

SELECT Count(*) AS CountOfbook, tblSurvey.book, tblSurvey.ID
FROM tblSurvey
GROUP BY tblSurvey.book, tblSurvey.ID
HAVING (((tblSurvey.book) Is Not Null And (tblSurvey.book)<>"Never"))
ORDER BY Count(*) DESC;

qryBook3

SELECT Sum(qryBook2.CountOfbook) AS SumOfCountOfbook
FROM qryBook2;

The query for the second field is pretty much identical to qryBook2. Then qryBook3 is combined with a query similar to qryBook2 (but for the other field) to get my total, which really isn't a total, but it atleast puts the two SUMs next to each other. I'll include that query again here:

SELECT qryBook3.SumOfCountOfbook, Sum(qryPopular2b.CountOfpopular) AS SumOfCountOfpopular
FROM qryBook3, qryPopular2b
GROUP BY qryBook3.SumOfCountOfbook;


Sure hope this makes sense!

 

[JonFer]

qryBook2 can get you the counts directly. You were grouping by book so it is giving you the count by book. In qryBook3 you remove the group by and get the total but that can be done in qryBook2 like this:

SELECT Count(*) AS CountOfbook
FROM tblSurvey
Where tblSurvey.book Is Not Null And tblSurvey.book<>"Never"
;

You can change your other queries similarly I'm sure.

What do you want your output to look like? A single row with the two counts?

 

[elinorigb]

Ah, its so simple. Thanks for that post. I changed those sum queries accordingly. As for the output of qry3, i just want one number, that is, the sum of qryBook2 and qryPopular2. I can probably figure that out, but i'm open to suggestions. thanks again for the help!

 

[PHV]

Where tblSurvey.book Is Not Null And tblSurvey.book<>"Never"
This where clause should suffice to get rid of the null values too:
Where tblSurvey.book<>"Never"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244