Help linking tables

[Guest]

Hi I am a new user to access 2007 and I'm having the following problem.

I have a table called "TBL_Man Process Details 1" which includes the fields
"Material A Description", "Material B Description" & "Material C Description"

I then have another table called "TBL_Raw Materials" which holds all the
data on raw materials. Obviously then for each product within "TBL_Man
Process Details 1" I can have up to 3 materials (A,B or C). The "Material
[A, B orC] Description" fields within "TBL_Man Process Details 1" are linked
to the ID field in the "TBL_Raw Materials" table, and this works fine when
accessing the table (drop down lists appear in each field to select the
material required.)

The problem is I want to bring the data together in a form, so I have
started a query on which to base the form. However I am having problems
bringing linked information together i.e by changing the links on the tables
(within the query) I can get the cost of "Material A Description" to be
brought in from "TBL Raw Materials". The problem is as soon as I try to get
the cost for "Material B Description" to be brought in, I get an error
message & no data is shown in the query.

Any help would be much appreciated!

Thanks Rob

 

[Carl Rapson]

You need to normalize your tables. What you need is a third table to be a
junction between the two you have:

Table: TBL_Process_Materials
Fields: Process ID (Foreign key to ID (primary) field in [TBL_Man Process
Details 1])
Material ID (Foreign key to ID (primary) field in [TBL_Raw
Materials])

In [TBL_Man Process Details 1] you would remove all references to materials;
these are handled in the new table. A side benefit is that you can have more
than 3 materials for a process.

Carl Rapson

 

[Guest]

Thanks a lot for the help I will now get it sorted!

Thanks

You need to normalize your tables. What you need is a third table to be a
junction between the two you have:

Table: TBL_Process_Materials
Fields: Process ID (Foreign key to ID (primary) field in [TBL_Man Process
Details 1])
Material ID (Foreign key to ID (primary) field in [TBL_Raw
Materials])

In [TBL_Man Process Details 1] you would remove all references to materials;
these are handled in the new table. A side benefit is that you can have more
than 3 materials for a process.

Carl Rapson

Hi I am a new user to access 2007 and I'm having the following problem.

I have a table called "TBL_Man Process Details 1" which includes the
fields
"Material A Description", "Material B Description" & "Material C
Description"

I then have another table called "TBL_Raw Materials" which holds all the
data on raw materials. Obviously then for each product within "TBL_Man
Process Details 1" I can have up to 3 materials (A,B or C). The "Material
[A, B orC] Description" fields within "TBL_Man Process Details 1" are
linked
to the ID field in the "TBL_Raw Materials" table, and this works fine when
accessing the table (drop down lists appear in each field to select the
material required.)

The problem is I want to bring the data together in a form, so I have
started a query on which to base the form. However I am having problems
bringing linked information together i.e by changing the links on the
tables
(within the query) I can get the cost of "Material A Description" to be
brought in from "TBL Raw Materials". The problem is as soon as I try to
get
the cost for "Material B Description" to be brought in, I get an error
message & no data is shown in the query.

Any help would be much appreciated!

Thanks Rob