visual access code request

jm saf 166 Reputation points
2021-01-14T18:45:34.51+00:00

Hi!

I have the starting table T1 in access 2013 with this data
56772-imagen.png

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

56677-imagen.png

Thanks in advance

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,710 questions
0 comments
Accepted answer
  1. Albert Kallal 5,246 Reputation points
    2021-01-16T02:31:23.607+00:00

    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:

    57189-qres.png

    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

    1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-01-14T18:52:07.56+00:00

    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?

  2. Xingyu Zhao-MSFT 5,366 Reputation points
    2021-01-15T06:22:18.213+00:00

    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.

  3. Viorel 116.5K Reputation points
    2021-01-15T09:40:18.487+00:00

    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?

  4. Viorel 116.5K Reputation points
    2021-01-15T17:36:16.923+00:00

    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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.