[Noob Question] Linking Serial Numbers to Product and Customer

[mrknight415]

Hi, I'm a total newb to access and am trying to setup what I feel is quite a simple database. I have a database with products, part numbers, and customers.

Here's is where I'm getting confused.

Suppose each product contains a set of part numbers, and each part number has a large number of parts that have been manufactured, each with their own serial number. I would like to be able to setup a database where I can quickly reference a customer, find out what products they're using, and also determine which serial numbers are being used on those products.

I feel this would be tedious, perhaps with each part requiring a table of serial numbers.



What would be the best way to do this?

[ranman256]

An example
youd need a tPart table.
[part#], [name],[manuf],[serial], and maybe [parent]

The parent field may be used when parts are composed of parts, ie, wheel
The wheel part would consist of smaller parts, like tire, hub, hub cap, valve.
Each of these small parts would have a serial # and a parent = tire.

[orange]

See this link for insight and example

Since you are an admitted newb, you should research Normalization. Also look for any/all info on hierarchies and hierarchical structure with database.