Counting query

[Guest]

Say I have 6 number fields. I need to run a monthly report counting the how
many times the same number came up. so if I have the same number in multiple
fields how do I write a query to be able to count to see how many times that
number was reported.

 

[John Spencer]

You probably will need to use a UNION all query as the basis of a totals
query.

Query One saved a YourSavedUnionQuery
SELECT Field1, DateFIeld
FROM YourTable
UNION ALL
Select Field2 , DateFIeld
FROM YourTable
UNION ALL
SELECT field3 , DateFIeld
FROM YourTable

Query2 using the saved query.
SELECT Month(DateField), Field1
, Count(field1) as TheCount
FROM YourSavedUnionQuery
GROUP BY Month(DateField), Field1


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..