I need a query to do this

[mscertified]

I need a query to do the following and cannot seem to figure out how to do
it. I'd like it done in a single query if possible.

The table contains columns for Userid, StartDate, End Date and Eligibility.

I need the query to return for each unique Userid between a given date
range, the total number of rows where Eligibility = "A" and the total number
where Eligibility = "B".

The result table should look like:

Userid TotalA TotalB
U1 0 10
U2 10 5
U3 5 0

I need the zeros in the total columns but not if the User has zero in both
columns.

 

[S.Clark]

I don't think you'll achieve the single query dream, but the rest is possible.

What you want is called a crosstab. The crosstab query is based on a
distribution query, to get the counts by Eligible value. You could use an
intermediate query to suppress the all zero records.