First, what a marvelous little problem!!!
So for the readers, lets get the question clear:
The poster wants a rank (count) of each user for a day.
So, user “5” might visit 1 or 20 times in one day, but he is the FIRST – gets “1”
User “8” might visit 1 or 20 times in one day, but he is the Second user – gets “2”
User “51” might visit 1 or 20 times in one day, but he is the Third user for that day – gets “3”.
So this is HOW we come up with "51" = 3 - he is the 3rd user/visitor for that day
Ok, now with the above problem?
Well, MS-Access sql does not have the ability to return a row_id in a query (ie:1, then 2, then 3).
And worse, we do NOT have “rank”
This is rare problem in which both of these features would and could be valuable here.
But, you can still do this without a temp table.
So, save a access query like this:
SELECT 1 AS tt, Z.User_ID, Z.Date, Z.Office_id
FROM Table1 AS Z
GROUP BY Z.User_ID, Z.Date, Z.Office_id;
I called the above “td”
Then we can solve this by doing:
SELECT Table1.User_id, Table1.Date, Table1.Office_id, (SELECT SUM(tt) FROM td
WHERE td.[Date] = Table1.Date AND td.Office_id <= Table1.Office_id and td.User_id = Table1.User_ID) AS num
FROM Table1;
And you get this output:
Note that the "cute" <= is a trick in access to get a "rank" which of course we have in sql server, but not in Access.
No matter how you slice this? This is a fantastic SQL question for a “test”. It simple, not a lot of columns, and no joins either!!!
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada