Ive Got the Many-to-Many Blues (1 Viewer)

I have 3 tables that I pulled from external sources, none of which came with ID fields or primary keys. I ran a simple sql statement on all three tables to add primary keys. The three tables are: tblRevByDirMgr tblAssumpByDirMgr and tblSFADetailByDirMgr. Each table has many fields that are common in the other two tables so I knew had to set up primary keys in each as a basis for establishing a relationship. I also know that I have to create my junction tables to join the others in one-to-many relationships. My issue is this: I've seen many tutorials on how to create junction tables and edit relationships when tables are empty in the design stage. But when I have imported tables that are already populated, how do I populate the Junction table with data from the other tables to create my foreign key fields? I tried an Update query with no success.

The tables and field names are in the attached. My goal is to join all three tables. I know that this probably must happen in 2 steps. I can't that far though until I get over this first hurdle. Hope I haven't omitted vital info. Help!

You have not supplied any specific information as to

1. which table is to be linked to which table
2. by which key or keys
3. by what type of relation

Show the tables produced by this exercise: column names, and mark the PK & FK

See attached.

you do not need a SQL statement to add PK's

What do you mean by that?

your data needs to be structured in such a way that you can relate items in each table to items in other tables. If you don't have suitable fields, you will not be able to relate your data.

I don't know if you saw the pdf attachment in my reply spikepl but I did design the 3 tables so that they each relateable fields. Those are the color coded fields in the pdf attachment. But when I queried the first two tables (tblRevenueByDirMgr and tblAssumpByDirMgr) and then queried that result against tblSFADetailByDirMgr, I got too many records back, many duplicates--even after aggregating each table prior to running the queries. So I thought I should create primary keys for the tables in order to establish a junction table to link them. The SQL statement I was referring to was just a way for me to add the primary key as query step that could later be part of a macro, rather than having to remember each month to go into the table design after the fact and add primary keys to all three tables. The SQL statement was like this:

ALTER TABLE tblRevenueByDirMgr
ADD RBDM_ID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY

Am I totally going in the wrong direction here?

I can't see that adding a surrogate PK will help at all.

assuming your data is GENUINELY related based on the fields you specify, then your problem is deciding why you have duplicates in a table that shouldn't have duplicates.

The Pk in one table needs to be a FK in another. So if you add a PK to one table, how do you get that key inserted as a FK in the related table. You can't without considering the natural key, which brings you back to your original problem.

I see what you're saying: even though I have fields that are common in all three tables, the fact that one table doesn't have a foreign key in it that matches the primary key of the table I want to join it to, means I'm barking up the wrong tree.

Oh boy....this is what happens when you extract data from a server and are limited by permissions as to what fields you can bring in.

Thank you. What you said makes perfect sense.