How do I achieve this?

  • Thread starter Thread starter wyster
  • Start date Start date
W

wyster

I am using Office 2003. I am working on this Access database for work to
maintain inspection information on some machines at work.
We have a bunch of codes that describe each problem found with a machine.
The objective is to be able to lookup a machine and automatically see all the
codes (and their description) that have been entered for this machine.

So far, I have built a table for the machines and one for the codes. The
problem is, I don't know how to build the relationship between tables in
order to be able to enter multiple codes for one machine, and be able to show
all that information in a report as needed.
Any guidance on this project would be greatly appreciated. Thank you.

Wyster
 
Assuming that you have an ID field in your Machines table - like MachineID -
that is the Primary Key, then you would add a MachineID field to the Codes
table as a Foreign Key and link the tables on that field.
 
wyster said:
I am using Office 2003. I am working on this Access database for work to
maintain inspection information on some machines at work.
We have a bunch of codes that describe each problem found with a machine.
The objective is to be able to lookup a machine and automatically see all
the
codes (and their description) that have been entered for this machine.

So far, I have built a table for the machines and one for the codes. The
problem is, I don't know how to build the relationship between tables in
order to be able to enter multiple codes for one machine, and be able to
show
all that information in a report as needed.
Any guidance on this project would be greatly appreciated. Thank you.
Click to expand...

What you have is called a many-to-many relationship. You have many codes,
each of which could apply to more than one machine. To resolve this, you
need a map table, which will only contain information about the relationship
between the other two tables.

So, presuming you have:

tblMachine
MachineID
MachineDesc
etc.

tblProblemCodes
CodeID
Code
CodeDesc
etc.

then you would have:
tblMachineProblems
MachineID
CodeID
ProblemDate

HTH;

Amy
 
Amy picked up on the m:m relationship you are describing. Use the new third
table she describes to resolve that ...


Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top