Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count Unique IDs by Month

Status
Not open for further replies.

mkallover

Programmer
Feb 6, 2008
88
US
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:

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

I'm not entirely sure where I'd even get started on this one.
 
I would first create a totals query like:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top