How to link 2 tables in Acces with data arranged in a different way in a spreadsheet

[sg2808]

Hello,

I have an excel spreadsheet where I have the data. The columns I am interested in are:
Ref_no, Rule_detail, Location1, Location2, Location3.

In my access db, I have three tables with fields:
1. tbl[Rule_details]
RuleID
Ref_no,
Rule_detail

2. tbl[Location]
LocationID
LocationName

The above two tables are linked as many to many.

3.Junction table[RuleDetail_Loca tion]
RuleDetail_Loca tionID
RuleID
LocationID

I have uploaded the data in the tables 1 and 2. Now I have to populate the Junction table [RuleDetail_Loca tion].

From the spreadsheet, I obviously know how are the locations linked to a Ref_no.

I am struggling in my head about how to populate the Junction table using SQL.

Could someone please help?

Many thanks,
SG

[MikeTheBike]

Hi

Based on the assumptions that Ref_No, and LocationName are 'unique' fileds in their respective tables (if not it cannot be done!), then I would tackel it this way

For each row in the spreadsheet you run a select query on each of th 2 table to returs the 'ID' value for each Ref_No, Location1, Location2 and Location3 and the then execute an append query on the joining table for the three combinations of Ref_No/LocationID.

I don't know what primary key you have in the joining table, but you may need to check this for existing records first?

HTH


MTB

[sg2808]

Thanks MTB. I have mentioned the PK for the Juntion table.
Yes, the "Ref No" and the "Location Names" are unique. I will try what you have suggested.

Many thanks,
SG

[sg2808]

After getting some idea, this is what I did:

I have the master spreadsheet where I know how the data is linked to each other.

In my junction Table, apart from the FKs IDs in the table (PK of Table A and Table B) , I also added two fields, one that is common to Table A and the other that is common to Table B.

I then imported the data to the Junction table (of course the FK fields are still blank).

I then did a Update Design query. I did a "Join" of the common fields in the Junction table with Table A and Table B. There will be two updates required for the two FK fields.

In the criteria, I wrote TableA.Fieldana nmeA = Junctiontable.F ieldnameA and similarly, TableBFieldname B=Junctiontable .FieldnameB.

Then, Run the query... and the table was populated.

Once the FK IDs are populated, I deleted the common fields in the Junction table.

PS - @Thanks for your advise MTB.