How to link one entry in one table to several entries in another

[MaelstromMage]

I am trying to create/update an engineering change notification(ECN) process at my place of business. I can usually fumble my waythrough very simple tasks in Access, but I am struggling a little with what Iwant to do now.
About a year ago I implemented the ECN process at work and createda very simple database with one table and one form. I set it up so whenthe database was open only the form appeared and a person entered theinformation on the form, which in turn updated the table. However, I needto improve our process so more information is captured and communicated withour factory. Originally, I did not capture a product's revision level inthe database. I thought the revision level on the drawing would beenough. Now I am wanting to run reports and retrieve what the revisionlevel a part was at depending on the ECN, but I am not able to do it with thecurrent setup.
I have a table titled "ECN" and the unique ID Accessassigns to each entry is the ECN number used in all documentation. The unique number is the only thingautomatically filled out on the form. Everythingelse is filled out by whoever is performing the ECN.
One ECN number may cover several parts on a product line. For example ECN 1006 may apply to thefollowing parts: 1223, 3221, and 4556. With the implementation of ECN 1006 part 1223 went from revision “A“ to “B,”part 3221 went from revision “F” to “G,” and part 4556 went from revision “B”to “C.” The revision would only becaptured on the updated drawings, but not tied to anything in the database.
What I want to be able to do is have someone open the ECN databaseand start filling out the form. On theform somewhere will be several blank boxes arranged in 2 columns. One column labeled“Part Number” the other “Revision.” They would then put a part number for eachpart affected by the ECN in a blank box under the column “Part Number” and thenthe revision for that part in the blank box next to it under the column “Revision.” I included a picture of what I think the form will look like.


What I think I need to do is create another table titled “PartRevision.” It would have at least 2columns titled “Part Number” and “Revision.” These would then be “somehow” inserted/linked to the blank boxesdescribed above. However, there has tobe some way to link the unique ECN number to every part typed into the blankboxes. In other words, let’s say thereare 8 rows to record parts and revisions. A particular ECN changes 8 parts and their revisions. How do I get the ECN number to belinked/attached to the parts and their revisions? Also, how can I let someone enter multiple part numbers and revisions to the form and have it update the table correctly? I think I will need several columns to do the last part, but I am not 100% certain.
I am not sure if this makes any sense to anyone else, but I canpicture what I want to happen, but I cannot seem to make it work.
Any helpful comments and suggestions are welcomed.

[orange]

You need a clear statement (5-8 lines) of WHAT you are trying to do in plain English. You don't need to guess at what tables are/may be required. (you can add more detail once you have the essence of the business described, clearly.)
Here's a free tutorial that starts with a business description then leads you through a process to define tables, fields and relationships. You have to work through it but you will learn by doing. Then you can apply what you have learned to your own situation/opportunity.

Good luck -- you do have to work through it.

[MaelstromMage]

Thanks. I will look it over.