Adding Data to a Linked Table Construction

[JonoGee]

Hi All,


I am pretty new to this, but after some help on some previous issues, I am learning slowly but surely, with the help of searching forums etc. What a great resource this has been!!
The problem I am looking at the moment is one I have struggled to find an answer to, or to be more accurate, what is the best practise way to achieve what I need.

I have a linked table which is connected to an API to my website, and/or a simple excel document. Obviously you can't add fields to a linked table. What I need to do however is add new data to each of those rows somehow through another table and form. This new data needs to be permanently linked to the original SKU (Primary Key). I have prepared a simple diagram of what i need to achieve...



Step 1 shows the Linked table on the left and a new table on the right where i need to add additional information to the matching SKUs of the Linked Table....

Step 2 shows the same tables, but the linked table receives another ROW (6). I need the new table to add this new row and leave the missing data missing, but the already completed data untouched.

I have tried update/amend queries etc with joins and god knows what but i never get the desired results.. Could you please guide me on what would be best practise to achieve this..
Thank you

[JonoGee]

Ignore the second photo. Its not even showing when I try to edit the post, so i can't get rid of it

[Micron]

If you're adding records via forms as you should be then this should be solvable with a main form/subform design. I'm assuming that since sku field is in both tables, the data is related somehow. If not, forget this answer. If you add record for Phil, the subform would be blank when you land on Phil record in main form. However, you must base main form on a query with an outer join (one that gives you all records from API table and related records from other table). Perhaps research form/subform if not familiar with the setup, but create the main form query before creating any forms IMO.

[Welshgasman]

I believe you would need a 1 to 1 join.
Not normally advised, but as you cannot amend the linked table to include the extra fields, that would be the way I would approach it.
So any time you add a record to your main table (the one that is linked), you add the PK as a FK to the extras table. ?
Likewise if you delete the record from main table.

Are you sure you cannot amend the table to include new fields? What is the reason?

[JonoGee]

Its an API from a website I am getting the data from and populating the linked table with. I am unable to add extra fields to that table.
Ideally I do not want to have to add the primary SKU itself to the secondary table.
I have tried a method now, and it involves running a simple Amend Query that places all the linked table into a regular table. I can then add new fields to this newly created table. And I am thinking I can just run the query when the form opens for the new table.
What do you think folks?

[Welshgasman]

Well if it is your table, I cannot see why you cannot add extra fields? How does the api populate it?

[June7]

Yes, importing website data to a local table is an option. Wouldn't that include the SKU key?

Didn't you say you already tried INSERT/UPDATE queries without success? What happened?

So far, nothing really explains why a form/subform approach is not best. Although, I don't understand why Micron said the main form RecordSource must be a query joining tables. I suggest main form bound to linked table and subform bound to local table. Set subform container Master/Child Links properties to the two SKU fields.