re-connecting tables that began life together

[miconian]

My raw data contains many fields, including fields A, B, C and D.

I ran queries to create two new tables: the A-B table and the B-C table.

I took the duplicates out of the A-B table and added an autonumber field, so now each unique A-B combination has a number. This will of course become the key field for the A-B table.

But now I want to add the unique A-B key field to the C-D table as a foreign key. After all, each C-D combination is connected to an A-B combination... but only back in the original raw data.

I am not sure how to do this. Most tutorials I read about Access seem to assume that the user is starting with a blank slate and building the database up one table at a time. But in my case, I am always starting with one giant table from outside Access, and breaking it up. But once I've broken it up, I have trouble getting the new tables to link together properly. The relationships all exist, but they only exist in the original giant table.

Thanks for your help and patience!

 

[miconian]

I've found an old thread on bytes.com in which a similar issue was discussed. Perhaps a glance at it will help clarify my question for anyone who is curious:

http://bytes.com/topic/access/answers/205054-how-make-composite-unique-index

I guess what I am trying to do, then, is to create a unique index that connects two fields, but is not the primary key.

 

[Jythier]

You need to make table C-D based off a relationship with AN-A-B and A-B-C-D, correct? (AN = Autonumber)
So make a maketable query with A-B-C-D and AN-A-B and connect A-A and B-B, and then have the table write the fields AN-C-D.

Does that make sense or should I explain in more detail?

 

[miconian]

A bit more detail would be wonderful. Thanks very much.

 

[miconian]

It's really the connecting A-A and B-B that I don't understand, I suppose. Especially since it's the A-B combination that's crucial.

To make this less abstract:

A: holding company
B: subsidiary

C: campaign name
D: campaign ID

So, you see, the idea here is to give the campaigns their own table, but to associate each one with the holding/subsidiary combination that it goes with. There is only one A/B combination possible per campaign, just as there is only one C/D combination possible per campaign.

 

[Jythier]

Okay.

So you have made a table, with three columns. ID, HoldingCo, Subsidiary

You have a second table that you made, with two columns, Campaign Name and Campaign ID.

But, you also have the original table, HoldingCo, Subsidiary, Campaign Name, and Campaign ID.

I would write a maketable query that took the ID/HoldingCo/Subsidiary table and the original table, join them together by HoldingCo and Subsidiary, and then write the ID, Campaign Name, and Campaign ID columns into the new table. Then, you have ID/A/B, and ID/C/D, joined via ID, and you have your relationship.

 

[Norie]

Why not have a table for Holding co?

Also some 'junction' tables if any relationships are many to many.