"Abhi" <go****@megadelfi.com> wrote in message
news:1d**************************@posting.google.c om...
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:<eP********************@rogers.com>... Not quite sure I follow.
Are you saying that if there are four rows with 3 in field1 and two with
3
in field2, you want to report 6? (In other words, you don't care which
field
it comes from)
Yes, I don't care which field it comes from. It should indeed return 6
in this case, but if there are 10 rows and EACH row contains 3 in both
field1 and field2 then it should report 10, not 20. And of course I
need all the possible values counts reported.
If so, try a Union query, and then do your counts on that query:
SELECT Field1 As TheField FROM MyTable
UNION ALL
SELECT Field2 FROM MyTable
How to do counts on that query? I can only think of creating a
temporary table with results of this UNION query and then querying
that temp table. Is there any more elegant way?
P.S. Doesn't counting this UNION query return 20 and not 10 in a case
described above?
Yes, you're correct that the union would return 20, not 10, in the case you
described. That's why we needed more information from you!
Okay, so you've got the following:
Id Field1 Field2
1 5 10
2 10 10
3 10 15
4 15 5
and you want to return
FieldValue Count
5 2
10 3
15 2
Correct?
Create a query that returns all of the unique values in the table and name
it qryUniqueValues:
SELECT Field1 AS LookupField
FROM MyTable
UNION
SELECT Field2 AS LookupField
FROM MyTable
Create a 2nd query that joins qryUniqueValues to your table and name it
qryIntermediate
SELECT MyTable.Id, MyTable.Field1, MyTable.Field2,
qryNewsgroupQuestionUniqueValues.LookupField
FROM MyTable
INNER JOIN qryValues
ON (MyTable.Field1 = qryUniqueValues.LookupField)
OR (MyTable.Field2 = qryUniqueValues.LookupField)
Create a 3rd query base that figures out the counts using qryIntermediate:
SELECT LookupField, Count(Id) As Total
FROM qryIntermediate
GROUP BY LookupField
Now, assuming you're using Access 2000 or newer, it's possible to combine
all of that into a single query, but I wanted to explain the steps:
SELECT MyData.LookupField, Count(MyData.Id) AS Total
FROM [SELECT UV.LookupField, MyTable.Id
FROM MyTable
INNER JOIN
(SELECT Field1 AS LookupField FROM MyTable UNION SELECT Field2 FROM MyTable)
AS UV
ON (MyTable.Field1 = UV.LookupField) OR (MyTable.Field2 = UV.LookupField)].
AS MyData
GROUP BY MyData.LookupField;
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
