If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Structure & Relationships
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 a 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. |
#2
|
|||
|
|||
Table Structure & Relationships
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 |
Thread Tools | |
Display Modes | |
|
|