Update - 22/11/20:
I think, I will be going with method of exporting the query as a new table and work on it. thanks
Removing the attachment.
Hello again everyone,
I have been slow in updating my database but have made some progress this month. I am again stuck on a issue and seek advice for the same. Before I tell my issue, I would clear some info about the database.
The setup we are using is this database is about construction projects where multiple apartments or units are in the project.
Some unit are assumed identical hence grouped Type A or Type B and so on. Each unit have a number or unit name like G01, G02, 101, 102 etc.
The tables and the relation setup is as shown in image (Thanks to MajP, I got a lot of help from him in setting all up)
In this setup we just enter data for a unit type group and in reports etc, same data is displayed for each individual unit. Entering data for a group is a very time saving instead of entering data for each individual unit.
Table "tbl_Items_To_Units" is the location where we add individual product to a room. If we add a wardrobe in a unit type, it will be displayed in each individual unit.
For example if Unit type A have 3 units G01, G02, 101. If we add a wardrobe in Unit type A, it will be shown in all 3 apartment units.
This generates 3 records upon querying:
Type A - G01 - Bed 1 - Wardrobe
Type A - G02 - Bed 1 - Wardrobe
Type A - 101 - Bed 1 - Wardrobe
Now the issue is when we go ahead on the procedure. Now we need to add additional data for each Wardrobe, which is unfortunately not identical. I setup a table as tbl_MatrixData and linked it with "Units to ItemID"
Type A - G01 - Bed 1 - Wardrobe --- measure date 14th --- Installed 18th
Type A - G02 - Bed 1 - Wardrobe --- measure date 15th --- Installed 19th
Type A - 101 - Bed 1 - Wardrobe --- measure date 16th --- Installed 20th
The problem now is that the records are being grouped for the 3 units here and the matrixData is not being linked with a unique ID. Shown below in image. The Master link is grouping the records for all 3 type of units.
I am not a professional and learning. I need suggestions on how I can link "MatrixData" table with a unique ID so that the records are not attached to a group.
What I thought that I can export a query with records to a new table (the top part of form) with an autonumber as primary key.
This actually works, but can cause problem if someone wants to make changes to Previously entered data. It is not convenient to re-export table after any changes.
Please advice.
Thanks in advance.
(Yes, also this is my first database, and it seems complicated for a newbie like me. It is possible that there are many errors or wrong formats in database. Please bear with me for my errors.