Multiple links from one table to another

[FSHOTT]

I have two tables one is a table of components which has a unique item number
as the primary key. The other fields are part numbers, component description,
supplier, component type, etc. The second table is an assembly and contains
within a given record several of the item numbers from the component table. I
would like to link the tables such that I can create queries and reports that
include the records with the component part numbers and/or other component
attributes. I tried joining multiple links to replicas of the table(i.e.
Component Table_x where x=1,2,3,4,etc.). That didn’t work. I would appreciate
any direction you could give me on this. Thank You ahead of time…….. I am
using ACCESS 2003 to develop this application.

 

[KARL DEWEY]

You did not include the structure of the Assembly table. Here is how I see
your tables.
Components ---
CompID – primary key
part numbers
component description
supplier
component type
etc.

Assembly ---
AssenblyID – primary key
Name
CompID – foreign key
QTY

There would be a one-to-many relationship between the Components table and
the Assembly table on the CompID field. An example would be ‘washers’ in the
Components table and listing six as being in the the Assembly table for a
wagon.

 

[FSHOTT]

Karl Yes you are correct my Assembly Table includes the CompID for say
Washers but there it also contains as seperate fields CompID's for say -
Nuts, Bolts, Lock Waskers etc. All of these type components are included in
the Comp Table and identified by the CompID so I have multiple links from the
Comp Table to the Assembly Table.

 

[KARL DEWEY]

my Assembly Table includes the CompID for say Washers but there it also
contains as seperate fields CompID's for say - Nuts, Bolts, Lock Waskers etc.
WRONG way to do it.
You have to set the tables up as 'chicken' and 'egg' or parent and child.
The higher assembly has many components. There is a one-to-many relation. I
had them backwards in last post.
Assembly ---
AssenblyID – primary key
Name

Components ---
CompID – primary key
AssenblyID – foreign key
part numbers
component description
supplier
component type
QTY
etc.

 

[KARL DEWEY]

The data would look like this --
AssembyID Name
1001 Banjo
2001 Wagon
3005 Wheel-barrow

CompID AssenblyID Part number Description QTY
2011 2001 A002 Wheel 4
2012 2001 B23 Wagon Bed 1
2024 2001 C51 Tongue, Wagon 1
2057 2001 X49 Axle 2
2001 3005 Z15 Handle 2
2011 3005 A002 Wheel 1
2058 3005 X51 Axle 1
2013 3005 B25 Bed 1

 

[FSHOTT]

Karl Yes this look like the right path. I think I have a many to many
situation where the Assembly is Table 1, The CompID Table is 2 and Contains
The CompID plus the component properties(P/N, Description, Type, etc.) and
the third table is the ComptoAssembly table which contains the AssemblyID,
each CompID associated with the AssemblyID and the quanity. Is this the way
you see it?

The data would look like this --
Table 1- Assembly

AssembyID Name
1001 Banjo
2001 Wagon
3005 Wheel-barrow

Table 2 - components
CompID Part number Description

2011 A002 Wheel
2012 B23 Wagon Bed
2024 C51 Tongue, Wagon
2057 X49 Axle
2001 Z15 Handle
2011 A002 Wheel
2058 X51 Axle
2013 B25 Bed

Table 3- Assemblytocomponrents

CompID AssenblyID QTY
2011 2001 4
2012 2001 1
2024 2001 1
2057 2001 2
2001 3005 2
2011 3005 1
2058 3005 1
2013 3005 1

where the primary key AssemblyID in Table 1 is linked to the foreign key
AssemblyID in Table 3 and the primary key CompID in Table 2 is linked to the
foreign key CompID in Table 3. Is the primary key in table 3 a combination of
the two foreign keys? If so how do I do this?
Thank You very much for your help & direction.........

 

[KARL DEWEY]

Create the one-to-many relationship between your table1 and table3. Also
between table2 and table3.
Create form - table1 (Assembly) and subform - table3
(Assemblytocomponrents). Set the Master/Child links between form/subform.
In the subform use a listbox for the CompID field to select components for
the Assembly.
You can create another set of form/subform to view components/assembly.

 

[FSHOTT]

Karl I have a related question to this post. I receive from my customer a
dataset(EXCEL) which contains as a row(record) the assembly and compID
data(multiple fields). I order to create the AssemblytoComponet Table 3 I
need to extract the data from the customer supplied dataset. I also need to
manipulate the dataset to create table 1. What is the best way to do this?
Can I create queries which in turn act as the Tables 1 & 3?

 

[KARL DEWEY]

Use an append query to pull the data from the customer dataset and add to
your tables.