how do i perform a Count(Distinct) query for 2 different column va

  • Thread starter Thread starter jay
  • Start date Start date
J

jay

how do i perform a Count(Distinct) query for 2 different column values using
GROUP BY

I have this "source" table like this....

|------------------------------------------|
|ROLLUP_ID | GAP_ID | REQ_ID | COST|
|------------------------------------------|
|001 | 001 | 002 | 12.00 |
|001 | 002 | 002 | 15.00 |
|005 | 005 | 009 | 9.00 |
|------------------------------------------|

I need a group by query with distinct for ms access 2007...

to make the following...aggregate query

|--------------------------------------------------------------|
|ROLLUP_ID | GAP_COUNT | REQ_COUNT | TOTAL_COST |
|--------------------------------------------------------------|
|001 | 2 | 1 |
27.00 |
|005 | 1 | 1 |
9.00 |
|--------------------------------------------------------------|

Any help would be so grateful...

I looked at this suggestion but I am not sure its applicable:

http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx
 
Jay -

You may need to create separate queries for each count(distinct) you want,
and one to calculate the total cost, then combine the separate queries in a
final query using the ROLLUP_ID as the join field.
 
Back
Top