Relationship question

[Garret]

Alright here's the deal:

I have three tables: tblMachines, tblMaintenance, and
tblMaintenanceInfo

tblMachines contains three fields, MachineID, MachineDesc, and
MachineGroup
tblMaintenance contains MachineID, Month, Department, and Requirements
tblMaintenanceInfo contains MachineID, MaintenanceNo, CompletedDate,
and CompletedBy

I want it to work such that the Machines in both tblMachines and
tblMaintenance are the same machine item, but there are different
fields involved. I think is this a One-One relationship? Correct me
if I'm wrong on anything so far.

tblMaintenanceInfo is designed in mind to be a subform for
tblMaintenance so there can be multiple completedDates and
CompletedBys. One-Many relationship?

How can I create relationships on all these tables so it works out how
I want it? So far tblMachines and tblMaintenance seem to be dealing
with different data.

Thanks. I can describe further if you have any questions.

 

[John Vinson]

Alright here's the deal:

I have three tables: tblMachines, tblMaintenance, and
tblMaintenanceInfo

tblMachines contains three fields, MachineID, MachineDesc, and
MachineGroup
tblMaintenance contains MachineID, Month, Department, and Requirements
tblMaintenanceInfo contains MachineID, MaintenanceNo, CompletedDate,
and CompletedBy

I want it to work such that the Machines in both tblMachines and
tblMaintenance are the same machine item, but there are different
fields involved. I think is this a One-One relationship? Correct me
if I'm wrong on anything so far.

It would only be a one to one relationship if you do maintenance on a
machine only once, and never again. It's apparently a one (machine) to
many (monthly?) maintenance episodes relationship, joining on
MachineID.

tblMaintenanceInfo is designed in mind to be a subform for
tblMaintenance so there can be multiple completedDates and
CompletedBys. One-Many relationship?

You probably need to use MachineID and Month as the joint, two-field
Primary Key of tblMaintenance (so you can enter multiple months of
maintenance for each machine, but not the same machine twice). Change
the name of the field Month while you're at it, that's a reserved
word. You would then need to add a matching Month field to
tblMaintenanceInfo so you can join on both fields.

How can I create relationships on all these tables so it works out how
I want it? So far tblMachines and tblMaintenance seem to be dealing
with different data.

Well... yes. Different tables, different data. That's normal. But they
*are* related, right? you're doing maintenance on a particular
machine?

Thanks. I can describe further if you have any questions.

Post back as needed, we'll try to help!

John W. Vinson[MVP]

 

[Garret]

Thanks for the reply, John. I'll tell you what more I can and answer
your questions.
I want to point out that the fields I listed are such because thats
what I thought I could use to join them all together. If you have
other fields I should add/subtract to make them all joined then I'm all
for it.

It works like this.
There are machines. Machines have certain characteristics like
Description and Group.
One Form allows the user to Add/Update/Delete machines with just these
three categories.
The Machine Maintenance form actually does something with the machines
(the maintenance of them), and so here is listed the data like
requirements(memo field), month (month of usual annual maintenance),
and Department (which section machine belongs to).
One idea I had originally was just to include this all on one table of
Machines, but therein lies the problem with the third table. I want to
have multiple maintenance dates that belong to a machine, but I thought
this information really shouldn't be listed along with all the machine
data, just the maintenance area of its data. Maybe it could work this
way? I don't know.

Well hope this clears things up. I'm on vacation next week and work
ends for me in 10 minutes. I'll communicate with you at my house later
or next Monday.
Thanks.

 

[John Vinson]

Thanks for the reply, John. I'll tell you what more I can and answer
your questions.
...
It works like this.
There are machines. Machines have certain characteristics like
Description and Group.
One Form allows the user to Add/Update/Delete machines with just these
three categories.
The Machine Maintenance form actually does something with the machines
(the maintenance of them), and so here is listed the data like
requirements(memo field), month (month of usual annual maintenance),
and Department (which section machine belongs to).

So it sounds like these fields are actually unique attributes of a
particular machine, if the "Month" means "Machine A312 is maintained
every year in June"... but that's not clear to me! If that is the
case, then I'd suggest putting these fields in the Machines table; one
to one relationships are really rather rare, and this isn't (IMHO) a
case where such would be required.

One idea I had originally was just to include this all on one table of
Machines, but therein lies the problem with the third table. I want to
have multiple maintenance dates that belong to a machine, but I thought
this information really shouldn't be listed along with all the machine
data, just the maintenance area of its data. Maybe it could work this
way? I don't know.

That's exactly what one to many relationships are FOR. If you're
making the common assumption that you must have the machine
description and the maintenance date in the same table, you *don't* -
that's what Queries and Subforms are for!

John W. Vinson[MVP]

 

[Garret]

Hello John, thanks for responding again.

So what you're saying is that it's probably the best decision to have a
setup with just two tables: Machines and Maintenance Info. I should
put all the attributes(Department, Month, etc.) from the Maintenance
table I have now into the table of Machines instead, to have a larger
Machines table, and just have the one-many relationship with Machines
and Maintenance Info. This does seem like the most reasonable way to
solve this problem.