How do I achieve this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wyster - 08 Jan 2008 04:10 GMT

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

Reply to this Message

Beetle - 08 Jan 2008 05:11 GMT

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.

Signature

_________

Sean Bailey

> I am using Office 2003. I am working on this Access database for work to
> maintain inspection information on some machines at work.
[quoted text clipped - 9 lines]
>
> Wyster

Reply to this Message

Amy Blankenship - 08 Jan 2008 15:47 GMT

>I am using Office 2003. I am working on this Access database for work to
> maintain inspection information on some machines at work.
[quoted text clipped - 9 lines]
> all that information in a report as needed.
> Any guidance on this project would be greatly appreciated. Thank you.

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

Reply to this Message

Jeff Boyce - 08 Jan 2008 20:30 GMT

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

>I am using Office 2003. I am working on this Access database for work to
> maintain inspection information on some machines at work.
[quoted text clipped - 11 lines]
>
> Wyster

Reply to this Message