populate after creating many to many relationship

[crazyinjun]

Hi. I am new to access and have been searching everywhere for some guidance on how to populate a junction table when trying to normalize the data and am looking for some guidance.

I have about 600,000 records in an excel sheet and tried using the table analyzer but I run out of memory. Instead i have broken up my giant table into a few other tables with their own primary keys and have populated from a maketable query. I related the tables together in a 1 to many and many to many table and one of the subforms is working great. The problem i am having now is with the many to many table (WorkedJunctionTbl). I don't want to have to manually input thousands of lines of data, so is there a way i can populate the data from the original data correctly using the new autonumber primary keys? I can't seem to get an append query to work correctly.



thanks in advance!

[June7]

There has to be some unique identifier already in place that can be used to relate records. This record ID should have been in place on the spreadsheet before breaking up and included in both datasets.

Build a query that joins on this common record ID and pull in the OpID and WorkcenterID fields to insert into junction table.

Why doesn't Workcenters have other fields - like CenterName, CenterManager?

[crazyinjun]

Thanks for the quick reply! I kind of see...but i guess that's where my confusion sets in. Isn't the point of normalizing the data to reduce the amount of redundancy? If i use the unique identifier from the originating table, then i'll have 600,000 records that it'll associate with. Will i delete the large table and relationship once it's populated?

After I have this data and understand how to populate it, I was going to input other fields relating to the workcenter...good catch and thanks again!

[June7]

Okay, think I did miss something. So after breaking up the spreadsheet you have x number of unique workcenters and y number of unique PartNumber/OpNumber records. The original spreadsheet had 600,000 combinations of these two sets?

What two columns in the spreadsheet had the workcenter and part number identifiers? Could just use those fields as the PK fields in each table. Then populate the junction table with those 600,000 combinations as the foreign key references.

If you want to then switch to the autonumber fields as PK/FK, those original fields can be used to join the 3 tables and pull in the autonumber fields to populate two more fields in the 600,000 records then remove the original two fields in the junction table.

When creating another association record in junction table, select the partnumber and the workcenter and only the IDs are saved. The reduction in redundancy is not repeating the PartNumber, OpNumber, OpDescription (as well as CenterName, CenterManager) info in junction table.

[crazyinjun]

yes..that is correct. X number of unique workcenters (350 of them), y number of unique partnumber/op number records (about 80,000). I also wanted to split the partnumber into its own table since it is unique (about 800).
I did set the partnumberID and workcenterID as its own PK and that's where i'm getting stuck at populating the junction table FK's. I can't figure out how to run my query to append the junction table. Can you expand on the 3rd paragraph where you recommended to join the 3 tables?

[June7]

Create a table from the spreadsheet that has the two identifier fields for the 600,000 records. This is the junction table. The OpNumbers and Workcenters tables also each need field with respective original identifier. Workcenters does not appear to have field for CenterNumber from the spreadsheet.

Now if you want to use the autonumber fields as PK/FK, add two new foreign key fields to the junction table. Build an UPDATE query that joins OpNumbers and Workcenters to the junction table on the original identifier values. Set the two new foreign key fields to update with the autonumber primary keys. When all looks good, remove the original fields from junction table.

[crazyinjun]

Thanks for all the help but I'm trying to understand the steps you have laid out but not really getting it to work in access. Are there any videos you can point me to that shows this process?

[June7]

No, I don't know any videos or tutorials.

How rough are the numbers you show - only 600,000 records? - 80,000 OpNumbers and 350 CenterNumbers can produce 28,000,000 combinations

Exactly which step is not clear? You need 3 tables created from the original spreadsheet.

1. table one needs at least two fields, the OpNumbers and Workcenters, should be 600,000 records, this is the junction table that associates OpNumber record with Workcenter record, one way to get this table is to copy the original import table and delete all other fields

2. another table will have the unique OpNumbers and associated data (80,000 records)

3. another table will have the unique CenterNumbers and associated data (350 records)

Getting tables 2 and 3 will each require an intermediate step that uses a query to reduce the 600,000 records to the respective unique records. Example:

SELECT OpNumbers, fieldname1, fieldname2, fieldname3 FROM OriginalImport GROUP BY OpNumbers, fieldname1, fieldname2, fieldname3;

or

SELECT DISTINCT OpNumbers, fieldname1, fieldname2, fieldname3 FROM OriginalImport;

Create table from that dataset.

Be aware that any variation in the data associated with OpNumber or CenterNumber will produce another record, even if the OpNumber or CenterNumber is same. These errors will have to be fixed (duplicates tracked down and eliminated) before the OpNumber or CenterNumber fields can be set as unique index.

Splitting the PartNumbers and OpNumbers will be a similar process.

Do you really want to assign autonumber as PK/FK? This is a separate issue that can be dealt with after the above is accomplished.

[crazyinjun]

finally got it! thanks for all your help and patience!