summing queries.

[joseph!!!]

i'm going to start this by telling what i am working toward in the end as it is where the problem obviously stems from.

i am trying to create a report which will tell me how many times 'hcapp' is in a table. there are three different fields where 'hcapp' can be placed...i can easily create three different queries which return how many times 'hcapp' is mentioned in one field, but i have yet to figure out how to look through all three fields in one query...is it possible? if not, how can i have a report call all three of these queries and then sum the total?

 

[chacal]

are the three fields in the same table?

If they are, you could create a query that will have three Count (one on each field), and then, in your report, you would create a formula in the textbox or label you will use to display your result. The formula would be something like:
=[query]![field1] + [query]![field2] + [query]![field3].

If they aren't, you could create 3 queries in the same way as above, and put the same formula in your report. But the name of the query will change depending of your field.

Hope this will help you.

 

[joseph!!!]

the three fields are within the same table.

should i then make a summing query and then put all three of those fields in it...and "Count" all of them...because when i do this, it returns that there is a data type mismatch...

i guess what im getting at is that i would like for you to make this a little more understandable for a complete access idiot.

 

[Rich]

Why could the same value appear in three separate fields?

 

[mresann]

Here is a query I just cooked up. The key is to create an alias table and subquery the actual table three times, once for each field.

SELECT DISTINCT

(SELECT Count(tblTest.FieldOne) AS CountOfFieldOne
FROM tblTest
GROUP BY tblTest.FieldOne
HAVING (((tblTest.FieldOne)="hcapp"));)

+

(SELECT Count(tblTest.FieldTwo) AS CountOfFieldTwo
FROM tblTest
GROUP BY tblTest.FieldTwo
HAVING (((tblTest.FieldTwo)="hcapp"));)

+

(SELECT Count(tblTest.FieldThree) AS CountOfFieldThree
FROM tblTest
GROUP BY tblTest.FieldThree
HAVING (((tblTest.FieldThree)="hcapp"));)
As SumOfText

FROM tblTest AS tblTest_1

You can replace "hcapp" with a parameter value, such as Forms!txtParameter.Value (from a form), or as part of another subquery.

There may be a simpler way (I'm not too fond of the DISTINCT method for calculations), but this will work as good as anything else.

EDIT: I've slimmed it down by changing the unnecessary aggregate subqueries and swapping them with simple count queries. I still have that doggone DISTINCT problem though, so it will work for small recordsets.

SELECT  DISTINCT

(SELECT Count([FieldOne]) AS FieldOneCount
FROM tblTest
WHERE (([FieldOne]="hcapp"));)

+

(SELECT Count([FieldTwo]) AS FieldTwoCount
FROM tblTest
WHERE (([FieldTwo]="hcapp"));)

+

(SELECT Count([FieldThree]) AS Field3Count
FROM tblTest
WHERE (([FieldThree]="hcapp"));)

AS TableCount

FROM tblTest