Best way to populate data

[jaryszek]

Hi Guys,

my whole model and RI are looking like this:





I have all data within separate tables and now i want to populate junction table to fullfull requirements.
I know that for example: Client 1 should have all topologies (so Topology1, Topology2....Topology6).
Version1 should have only Client 2 and also all topologies.

So Client2 : all topologies : and for each topologies can have differente versions.

Which ways do you know to populate junction table with all these numbers with foreign keys?
Sitting and manually writing sequances like 1,2,5,8,2,1 for all foreign keys it will be not easy...

Best,
Luke

[isladogs]

If every combination of two fields is possible, use an append query based on a Cartesian join I.e. No join between the tables
Otherwise create a function to loop through and create the values according to rules you set up.

Of course, it's best to create the junction table first before any data is added.
Then use code to populate the junction table as each new record is added to either of the two tables it connects

P.S. Is Luke the anglicised version of Jacek?

[jaryszek]

Hi ridders52!

thank you,
Sorry i am Jacek (Jacek is Jack) and my friend is Lukasz (Lukasz is Luke), i was thinking about him and his model and i have just made a mistake.

Of course i do not understand it for now so i have to bother you more ;-)

If every combination of two fields is possible, use an append query based on a Cartesian join I.e. No join between the tables

Hmm nice. Append query based on cartesiaon join. Can you have any examples or tutorials? I do not have idea how to go further in this method.

Otherwise create a function to loop through and create the values according to rules you set up.

How this function should work? Loop through each table and based on that fullfill junction table?
I can not imagine that,

thank you,
best wishes,
Jacek Antek

[isladogs]

I'll do you an example of each method.
Going out for a few hours so may not be till tomorrow

[jaryszek]

ridders52 love you !

thank you,
i am very happy that i will learn something new!

Best,
Jacek

[isladogs]

ridders52 love you !

How kind but forum relationships never last especially with you in Poland & me in the UK .....

As promised, the attached database shows ways of quickly adding records to a junction table used to create a one to many to one relationship such as that below



This is mainly intended for use when a change in design leads to a junction table being added after records have already been added to the two adjacent tables.
Normally the records should be populated automatically via a data entry form

For convenience in this example, 6 employees and 5 customers have been taken from the Northwind database.

The Orders junction table could in principle include up to 6x5=30 possible records. However, in real life not all records would be used
This is intended as a ‘proof of concept’ only and the methods should be adapted for your own purposes

The approaches use cartesian join queries or nested looped recordsets

Method 1
Cartesian join query used to append all 30 records

Method 2
As method 1 but with 10 selected records EXCLUDED

Method 3
Uses recordset loops to append all 30 records

Method 4
Uses recordset loops to append all 30 records then removes 5 selected records

NOTE: If there are many records to be added, the cartesian join will probably be faster than using recordsets

[jaryszek]

Awesome !!!

I will remember that and use in my code.
All cleared explained and with database sample.

Thanj you for help and support,
Jacek