How to generate a sequence of ID’s based on mapping tables and values from the forms in MS-Access (Sql)?

I want to generate ID’s based on the form values in MS-Access. And then for each ID generated, create a group of ID’s by adding another 4 digits in the end based on a Mapping Table, representing different octets for different time points (12 ID’s based on the Initial ID and the mapping Table).

For example, if the ID generated based on form values is 123456, I want to add another four digits and create a group of ID’s, say from a mapping table. Like,

123456**1111**
123456**1112**
123456**1113**

and so on.

So far each primary ID, I am slapping on four digits at the end and generating a group of 12 ID’s.

I am a beginner in Access and I have tried some code:

UPDATE Table1 SET GenID = UPDATE Table1 SET Table1.GenID = t1 (SELECT Map.V FROM MAP as t1)

However, I get a error that Access does not recognize Map as a valid Field or expression. I am able to break down the problem into this. But could not find a way further and design a query.

Sample Data: (The short_ID and Long_ID tables, uses the mapping tables below each of them as shown.)

Short ID Table:

    ----------------------------------------------------
    ID | Subject_ID | Organ_Type | Category | Short_ID
    -----------------------------------------------------
    1  | 100        | Kidney     |    A     | 100200300
    -----------------------------------------------------
    2  | 400        | Heart      |    B     | 400500600 

Mapping Tables for Short ID:

Map1 for Table1:

---------------------
Map_from  |  Map_to |
---------------------
Kidney    | 200     |
Heart     | 500     |
---------------------

Map2 for Table1:

    -----------------------------
    Map_cat_from  |  Map_cat_to |
    -----------------------------
    A              | 300        |
    B              | 600        |
    -----------------------------

Long ID Table:(shown here are just examples for 2 time points rather than 12)

---------------------------------------------------
Subject_ID | Short_ID   | Long_ID  Timepoint       |
---------------------------------------------------
100        | 100200300  |  1002003000001           |
---------------------------------------------------
100        | 100200300  |  1002003000002           |
---------------------------------------------------
400        | 400500600  |  4005006000001           |
---------------------------------------------------
400        | 400500600  |  4005006000002

Timepoint Map for Long ID Table:

------------------------------
Timepoint | Value_to_append  |
------------------------------
 1        | 0001             |
------------------------------
 2        | 0002             |

I need to generate these short and long ID’s from the mapping tables directly when input is given in the form. (Category, Organ_Type, Subject_ID)

tldr

1. generate id from mapping table and form values (id creation)
2. add four digits at the end and create a group of 12 id’s (long id creation) based on a mapping table (which has the 12 four digits that is to be appended in the end)

Answer

First, create a query, QShortID:

SELECT 
    Table1.ID,  
    Table1.Subject_ID, 
    Table1.Organ_Type, 
    Table1.Category, 
    [Subject_ID] & [Map_to] & [Map_cat_to] AS Short_ID
FROM 
    (Table1 
INNER JOIN 
    Map1 
    ON Table1.Organ_Type = Map1.Map_from) 
INNER JOIN 
    Map2 
    ON Table1.Category = Map2.Map_cat_from;

Output:

Next, create a query, Dozen, that will return 12 rows:

SELECT DISTINCT 
    Abs([id] Mod 12) AS N
FROM 
    MSysObjects;

Finally, create a Cartesian (multiplying) query, QLongID:

SELECT Table1.ID, Table1.Subject_ID, Table1.Organ_Type, Table1.Category, [Subject_ID] & [Map_to] & [Map_cat_to] AS Short_ID
FROM (Table1 INNER JOIN Map1 ON Table1.Organ_Type = Map1.Map_from) INNER JOIN Map2 ON Table1.Category = Map2.Map_cat_from;
SELECT 
    QShortID.Subject_ID, 
    QShortID.Short_ID, 
    [Short_ID] & Format([N] + 1, "0000") AS Long_ID
FROM 
    QShortID, 
    Dozen
ORDER BY 
    [Short_ID] & Format([N] + 1, "0000");

Output:

Edit:

To use the timepoint mapping, use:

SELECT 
    QShortID.Subject_ID, 
    QShortID.Short_ID, 
    [Short_ID] & [Value_to_append] AS Long_ID
FROM 
    QShortID, 
    TimepointMap
ORDER BY 
    [Short_ID] & [Value_to_append];

Output: