Need help building database

[PenniLane]

I am building a new database (and am a new Access user) and I have run into a
problem. I am working with part numbers. One list is my company's numbers,
three other lists are competitors numbers. I need to use my database to look
up part numbers using any of these numbers. My "Part Search" works, except
for where I am stuck. My company has some part numbers that are kits made up
of 2 or 3 or the competitor's part numbers. I have no way to know that those
part numbers are part of a kit including other numbers. I need a way to show
all part numbers in the kit without modifying my part numbers. Anyone have
any suggetions?

 

[Ron2006]

Here are some ideas since no one has touched it so far:

1) DO NOT have 3 (or 4 etc) tables.

2) possible tables

tblCompany
CompanID - PK
CompanyName (Including one record for your own company
other company info

tblPart
PartID - Auto Number
PartNumber - PK
CompanyID - PK the Company ID of who makes it.
Description
other Part info

tblComponents
ID - PK
PartID - FK The partID of the part that is the master part number
(for instance Standard Box for Widgets)
ComponnentPartID - FK (the PartID of one of the parts that makes up
the master part number (for Instance - Lock for a box.)


This way you can have the same part from a company used on multiple
boxes AND can have as many parts as needed and from as many sources as
needed.
This also allows you to have composit parts made themselves from
composit parts.

Hope this gave you some ideas...

Ron

 

[PenniLane]

Thank you very much! A Components table is what I was looking for. Now I have
a lovely form to look up parts by ANY part number, including the component
parts. I really appreciate your help!