visual access code request

Question

Hi!

I have the starting table T1 in access 2013 with this data

I need a visual code to create a new table with the field num where it indicates the number of different centers in which the user is on the same day. The output table should be T2

Thanks in advance

Answer 1

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

Answer 2

Since you already have the code that deals with DataTable objects, then check this example and adjust it for your needs:

Dim T1 As New DataTable
T1.Columns.Add("userid", GetType(Integer))
T1.Columns.Add("date", GetType(DateTime))
T1.Columns.Add("office_id", GetType(Integer))

Dim T2 As New DataTable
T2.Columns.Add("userid", GetType(Integer))
T2.Columns.Add("date", GetType(DateTime))
T2.Columns.Add("office_id", GetType(Integer))
T2.Columns.Add("num", GetType(Integer))

T1.Rows.Add(1, "01/01/2020", 5)
T1.Rows.Add(1, "01/01/2020", 5)
T1.Rows.Add(1, "01/01/2020", 8)
T1.Rows.Add(1, "01/01/2020", 8)
T1.Rows.Add(1, "01/01/2020", 51)
T1.Rows.Add(1, "02/01/2020", 5)
T1.Rows.Add(2, "15/01/2020", 14)
T1.Rows.Add(2, "15/01/2020", 23)
T1.Rows.Add(2, "16/02/2020", 23)
T1.Rows.Add(2, "17/02/2020", 23)
T1.Rows.Add(10, "01/01/2020", 5)
T1.Rows.Add(10, "15/01/2020", 10)

Dim q1 = T1.AsEnumerable _
    .Select(Function(r) New With {.userid = r.Field(Of Integer)("userid"), .date = r.Field(Of DateTime)("date"), .office_id = r.Field(Of Integer)("office_id")})

Dim q2 = q1 _
    .GroupBy(Function(d) New With {Key d.userid, Key d.date}) _
    .Select(Function(g) New With {g.Key, .Values = g.Select(Function(v) v.office_id).Distinct.OrderBy(Function(o) o).Select(Function(o, i) New With {.office_id = o, .num = i + 1})}) _
    .SelectMany(Function(d) d.Values.Select(Function(v) New With {d.Key.userid, d.Key.date, v.office_id, v.num}))

Dim q3 = q1 _
    .Join(q2, Function(a) New With {Key a.userid, Key a.date, Key a.office_id}, Function(b) New With {Key b.userid, Key b.date, Key b.office_id}, Function(a, b) New With {a.userid, a.date, a.office_id, b.num})

For Each r In q3
    T2.Rows.Add(r.userid, r.date, r.office_id, r.num)
Next

Answer 3

It seems that you need an SQL query like this:

select *, 
    dense_rank() over (partition by userid, [date] order by [date], office_id) as num
from T1

Then insert results to T2. Do you want to find an equivalent query for Access or to calculate num in VB or VBA?

Answer 4

Hi @jm saf ,
Basically, you need to create a new table 'T2'.
Then use the following sql statement to copy T1's data to T2.

INSERT INTO T2 SELECT * FROM T1  

Finally, create a new column 'num' in the existing table and add some data in it.
Hope it could be helpful.
Besides, if you need further assistance, please provide more details about your 'num' column.

Best Regards,
Xingyu Zhao
*
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi
You do not explain what you want in enough detail.
What / where is 'field t'?
What constitutes the 'different centers' - something to do with the offices?
In your T2 table, please explain how the 'num' value of 3 is derived for office_id 51?