Count Unique IDs by Month

[mkallover]

I've got a table set up like this:

TransID(key), MemberID, TransType, Timestamp

What I'd like to do is create a query that would count the number of unique MemberIDs for each TransType and display by month. Like this:

           |Jan |Feb |Mar |
---------------------------
TransType1 | ## | ## | ## |
TransType2 | ## | ## | ## |
TransType3 | ## | ## | ## |

I'm not entirely sure where I'd even get started on this one.

 

[dhookom]

I would first create a totals query like:

SELECT DISTINCT MemberID, TransType, Format(TimeStamp,"mmm") as Mth
FROM [set up like this]

Then create a crosstab based on the totals query with TransType as the Row Heading, Mth as the Column Heading, and Count of MemberID as the value.

Duane
Hook'D on Access
MS Access MVP