Populating new junction table

[rbrule]

I recently received information on how to create a junction table, and I thank those involved for their input. However, both scenarios involved creation of the junction table and the host tables from scratch.

My problem is I have two tables with existing data that I need to link. How do I populate the junction table with the IDs from the two existing tables. I tried to create a table from one and append data from the other, and it did not work.

Or is there another way to link these two tables? Neither has a unique field with a corresponding field in the other table.

 

[rbrule]

In theory you could do this with an append query but there is a problem. How would you choose which record in tblA is related to which records in tblB? There are no existing linking fields that define the relationship and I'm certain that you don't want every row in tblA related to every row in tblB.

That is my dilemma. There are similar fields in both tables. I joined four fields of the same type in a query and got some results, but I am not convinced of the accuracy. I tried to make a table from the results but access would not let me. Do you have any suggestions?

 

[rbrule]

It makes no logical sense to automatically generate rows for a junction table. What you are asking is akin to asking someone to create the class rosters from a list of 300 freshmen and 45 possible classes. How do you know who wants to take Algebra? Who's taking typing? Without that knowledge, there is no way to assign students to classes in the junction table.

You need to come up with some other data source that details the relationships if they already exist. If the datasource is machine readable, we'll be able to help you work out the append query. If the datasource is text, you're on your own. You'll just need to enter them manually.

Thank you for your input