Search code examples
sqlvbams-accessduplicatesautonumber

Access SQL/Logic for Incrementing Key/Field based on composite key

Suppose you have a table in a database that tracks ticket appearances across 9 office locations for between 4 and 5 times periods every day. In each of these time periods, people might appear for one ticket (I guess we can use a transaction model), or they can appear for many (I've seen some with 15, heard of others with up to 50).

A query was created using Access 2013 to capture the data from this table. This is done for archiving (the current date table records are purged after 24 hours), and general analysis.

The current query logic is as follows:

SELECT Format([T1]![APP_DTE],"Short Date") AS APPEAR_DTE, 
T1.OFFICE, OFFCDES.OFFICE_NAME, OFFCDES.LOC_CDE, OFFCDES.LOC_NAME, 
T1.APP_TIME_PER, T1.ROOM_NUM, T1.INCL_CDE, T1.ID_NUMB, 
T1.ADJ_APPEAR_NUM, T2.APPEAR_CDE, 
IIf([T1]![APPEAR_LOGIN_TIME] Is Null,"",[T1]![APPEAR_LOGIN_TIME]) AS LOGIN_TIME1, 
IIf([T1]![OFFICE_LOGIN] Is Null,"",[T1]![OFFICE_LOGIN]) AS OFFICE_LOGIN, 
IIf([T1]![DECISION_TIME] Is Null,"",[T1]![DECISION_TIME]) AS DECISION_TIME, 
[T1]![ID_NUMB] & "-" & CStr([T2]![OFFICE_REP_ID]) & "-" & CStr([T1]![APP_DTE]) AS CASEKEY, 
Weekday([APP_DTE]) AS NUM_DAY, 
[T1]![OFFICE] & "-" & Weekday([T1]![APP_DTE]) & "-" & [T1]![APP_TIME_PER] AS OFFICE_TP_KEY, "" AS RecordKey
FROM (T1 INNER JOIN OFFCDES ON T1.OFFICE = OFFCDES.OFFICE) 
INNER JOIN T2 ON T1.APPEAR_NUM = T2.APPEAR_NUM
WHERE (((Format([T1]![APP_DTE],"Short Date"))=Format(Date(),"Short Date")));

I realize that the above code will need to be cleaned up for readability, and that it's probably not as efficient as it could be. That part I can't take credit for, I inherited all of this from a coworker and have just started to wrap my head around it.

The query result includes a ton of information, some (most) of which is irrelevant to what I'm trying to accomplish. It's important to capture all of this information since the information, once gone, is gone. With that stated:

What I'm trying to do:

I'm trying to incorporate a key/sequentially numbered column that would tell me how many times a particular ID_NUMB has occurred at a particular Office/Time Period on the date pulled. I'm also not looking for a total, rather something along the lines of the SEQ column here:

ID_NUMB  |  APPEAR_NUM  |  OFFICE  |  TP  |    DATE    |  SEQ
1        |  1XZ2        |  01      |  01  |  9/7/2016  |  1
1        |  1XZ3        |  01      |  01  |  9/7/2016  |  2
2        |  3MN1        |  03      |  01  |  9/7/2016  |  1
3        |  J915        |  02      |  01  |  9/7/2016  |  1
1        |  1TY1        |  01      |  01  |  9/7/2016  |  1
3        |  P91D        |  05      |  01  |  9/7/2016  |  1
2        |  U11E        |  11      |  05  |  9/7/2016  |  1

While I have looked around and found many fine numbering solutions, most aren't quite what I'm looking to accomplish due to how fine grained the count seems to be.

I've thought about writing the data to a temp table, but using some of the information that I've found didn't lead to any kind of solution. Similarly, I tried writing a separate query that could retrieve this information and add the sequential number in, kind of an adaption of the solution found on the MS Answers site. I put that aside as it seemed like the wrong approach.

I even toyed with writing the query to a temp table and then having a piece of VBA handle the sequential numbering, but that approach too, didn't get too far.

What would the right approach to generate a sequential number in a column as I outlined be? I'm at a loss.

Solution

  • I think this SQL might be close to what you want (based on the limited example) It does an INNER JOIN on the table T1 - and does a simple sort before it builds the SEQ on the fly

    SELECT 
    a.ID_NUMB,
    a.ADJ_APPEAR_NUM,
    a.OFFICE,
    a.APP_TIME_PER AS TP,
    Format(a.[APP_DTE],"Short Date") AS APP_DATE,
    (SELECT Count(ID_NUMB) 
        FROM T1 b 
        WHERE (b.ADJ_APPEAR_NUM <= a.ADJ_APPEAR_NUM
        AND b.ID_NUMB = a.ID_NUMB 
        AND b.OFFICE = a.OFFICE
        AND b.APP_TIME_PER = a.APP_TIME_PER
        AND b.APP_DTE = a.APP_DTE)) AS SEQ
FROM T1 AS a
ORDER BY a.ID_NUMB, a.OFFICE, a.ADJ_APPEAR_NUM;

    Query Results (using your data for T1)

    Sample Query Results