Help with data in two tables in one database

[jmallow]

I have a database for hardware inventory with the following fields: ID, Part Number, Quantity, Description - Revision, DrawerNum, DrawerDesc, CofC.

The fields DrawerNum, DrawerDesc, and CofC(stands for Certificate of Compliance) are all listed in another table. They are drawers with hardware in it, and the contents of the drawers never changes, so I figured it would be wise to put that info in another table.

The DrawerNum, DrawerDesc, and CofC all need to be attached to each piece of hardware for searching purposes used in quotes(to see if the hardware is in stock for machined parts).

What is the best way to do this? I have a field with just the drawer number for each ID from when I imported from excel. I don't mind manually editing each entry if needed. But how can I select a combobox dropdown for a drawer and have it fill out the information in the relevant fields without selecting each field one by one? One way I thought was just have a "Drawer" field in my hardware table that stores data like "1 - Helicoils - Yes-Certificate of Compliance" or something like that.

Any help here would be greatly appreciated.

 

[ssoltic]

If I got you right.

Create two tables:
first: ID, Part Number, Quantity, Description - Revision and Drawnum
second: DrawerNum, DrawerDesc, and CofC

create relationship "one to many" from second to first table (Drawnum is primary key)

After that you can create cbox and choose:

"1 - Helicoils - Yes-Certificate of Compliance" or something like that.