count of one thing and count of another ?? LOL

[james_lankford]

sorry for the poor title, but I can't think of a simple way to describe it

here are 3 tables that store the history of every transaction made by every user at automated teller machines (atm)

-----------------------------------

table name -- transaction

fields
transaction id -- primary key
user key -- link to user_transaction table
atm key -- link to atm_transaction table
transaction date time -- date time the transaction occurred

-----------------------------------

table name -- user_transaction

fields
user key -- primary key
user id -- user id (duh) , can occur multiple times in this table becasue each user can have multiple transactions

-----------------------------------

table name -- atm_transaction

fields
atm key -- primary key
atm id -- atm id (duh) , can occur multiple times in this table becasue each atm can have multiple transactions

-----------------------------------

these are history tables only

there is a different user table and atm table that stores user name, user address, atm address and so on
we are not interested in those tables

what I want to find out is
the number of atms visited by the number of uniques users

so
20 different atms had 20 unique users
17 different atms had 41 unique users
11 different atms had 3 unique users
1 atm had 57 unique users

--------------------------------------

I don't need the IDs to show up in the final result
just the two columns of numbers

I did this by putting some stuff in excel and doing a pivot table and then making a 2nd pivot table based on the first pivot table
but I know there must be a straight sql way
probably with some transform, partition, over something, something

any ideas ?
--------------------------------------

 

[ranman256]

(ms Query or ms Access)

Q1 : get all AMTs and unique users , set query property UNIQUE VALUES = TRUE
Q2 : count users / ATM in Q1