By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

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

P: 91
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_Location]
RuleDetail_LocationID
RuleID
LocationID

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

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
Apr 19 '12 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
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
Apr 19 '12 #2

P: 91
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
Apr 19 '12 #3

P: 91
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.FieldananmeA = Junctiontable.FieldnameA and similarly, TableBFieldnameB=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.
Apr 23 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.