I am currently running the following query to total the distinct dates in the data field and need to add another datafield. Is there a way to add another distinct count from the same source and output on the same query.
My current query:
SELECT x.MemberID, Count(x.data) AS data
FROM (SELECT DISTINCT MemberID, data FROM Table1 WHERE Criteria="2011") AS x
GROUP BY x.MemberID;
I was thinking of some sort of union which also pulls the data fields into single rows within the query. Something like this? Although it is very important that the dates are DISTINCTLY COUNTED.
Select MemberID, "data" as DataEvent, Data as Date from Table1
WHERE (LEN(NZ([data])) > 0)
UNION ALL Select MemberID, "data2" as DataEvent, Data2 from Table1
WHERE (LEN(NZ([data2])) > 0)
ORDER BY MemberID, DataEvent DESC;