Table Structure & Relationships

[NAHolmes]

Newbie here.

I'm trying to create relationships between tables & correctly use Primary
Keys.


I need to import data from Excel into a table which will have only 3 columns:

QTY - Ref - Drawing

None of the rows will have unique data (i.e. the same data for Ref & Drawing
could appear more than once).

This table will be 'Take-Off'



For each 'Ref', I need another table that lists all of the sub-assemblies
for that Ref.

In that table, there will be multiple rows with the same data for 'Ref', one
row for each sub-assembly.

This table will be 'Parts'


For each Part, I need another table that lists all of the sub-components for
that Part.

In that table, there will be multiple rows with the same data for 'Part',
one row for each sub-component.

This table will be 'Components'


my questions are:

1. Do I need a primary Key in each table?
2. If so, is the only option is AutoNumber?
3. Have I got the structure totally wrong?


I can produce a Query to produce the total Parts & shown this on a Report.
But it all goes horribly wrong when I try to extend the total funtion to
Components.

What is confusing me most, is that the data needs to have same value in many
rows, making the logic of a primary key ant applicable.

I have tried to disect the 2007 example Northwind, but it's much too
complicated for me to clearly see the logic.

Any help would be gratefully received.

 

[Fred]

What you have is a multi-level BOM (Bill of material). One of the more
complex structures, except with the one simplification that you have limited
it to three levels. A tought one for you to starte with being new to
Access.....you'll want to read up on structure (excellent references have
been given in other posts in this forum.

First a couple of questions

1. We presume that one TakeOff can have several subassemblies, and that
a subassembly can have many components. How 'bout the reverse? Can a
component be used on several different subassemblies? Can a sub assembly be
used on several different takeooffs? I'm going to guess that the answer is
yes. If the answer is no, ignore the second half of my answer.


2. Do you have (or are you going to create) some numbering system which
assigns a unique indentifying number to components, assemblies and takeoffs
(like a part number or takeoff number? If so, those numbers should probably
be your primary keys, and so would not be autonumbers. I'm going to assume
this for the rest of this post. If not, just make them autonumbers (only)
wherever they are PK's. Most tables need a primary key, and for the
others......IMHO it's simpler to just put a primary key in every table rather
than restructure them later when you find that a needed on has been left out.



If your answer to number one is yes, your have Many-To-Many relationships
between each of your levels, and need junction tables in between them. Some
people describe junctions tables more as just linking mechanics, I tend to
them of them as lists of real world entities....."instances of linkage" which
may have fields other than the usual 2 (or 3)

Your answer will be a book, not a post, but here's an outline for the first
page:

Shorten my long explanatory names, and add in fields with your info about
each of the entities


Table: TakeOff
PK = TakeOffNumber


(Junction) Table InstancesOfAPartSubassemblyBeingUsedInATakeOff
FK (Foreign Key): TakeOffNumber
FK PartSubAssemblyNumber
PK is optional and NOT one of the above 2


Table: PartSubassembly
PK: PartSubAssemblyNumber


(Junction) Table: InstancesOfUsageOfAComponentInAPartSubAssembly
FK PartSubAssemblyNumber
FK ComponentNumber
PK is optional, and NOT one of the above 2

Table: Components
PK: ComponentNumber

Link all fields to ther namesakes

Hope this helps you get a start.....should be a good foundation. Again, if
your answer to either question under #1 is "no", tell us because the answer
will be simpler. And if you don't have and aren't creating those unique
numbers, just change thos fields (only) where they are PK's to Autonumber