table relationship

  • Thread starter Thread starter vbnetman via AccessMonster.com
  • Start date Start date
V

vbnetman via AccessMonster.com

I have an equipment maintenance db. Within it there are 3 tables causing me
some confusion and I am requesting some assistance. Here’s what I’ve got;

Tblworkorders
Workordernumber – PK
Date
Other work order info

Tblworkorderdetails
detailsID – PK
ref_workOrderNumber – FK to tblworkorders

tblPMschedule
unitid
pmname
pminterval
ref_detailsID – FK to detailsID
PMcomplete

Some notes on the design of the pmschedule table. The PK is a composite
consisting of the first 3 fields. I’m aware that this is not recommended and
that there may be consequences under certain conditions. Here’s a few
possible entries in the table;

Unitid – 1
Pmname – A
PMinterval – 50
Complete? – yes

Unitid – 1
Pmname – A
PMinterval - 500
Complete? - no

Unitid – 1
Pmname – A
PMinterval – 1000
Complete? – no

So, in this example, a unit can have an assigned PM-a with different
intervals like 50 hours, 500 hours or 1000 hours. Anyway, I need to get to
the point. I have a one to many relationship between the details table and
the schedule table. A details record can include many PM’s. But here is where
I become confused. Is this really a one to many relationship? A single detail
record can only have one PM making this a one to one relationship. While I
remain pondering, can someone shed some light?

Thank you
 
How many PMs do you have? How many detail records can each PM have? Can
more than one detail record have the same PM?

Forget about your table structure for a moment and describe the real world
situation you are dealing with.

I suspect that you have a one-to-many relationship between PMs and detail
records. A detail record can only have one PM (true or false?), but a PM
can have many detail records (again, T/F?).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff and thank you for responding.
1.There can be an unlimited number of PM's - these will be defined by the
user and is entirely dependent on the type and number of pieces of equipment.
2. A work order can include documenting the fact that one or more PM's have
been completed. However, the detail record of the work order is where this
will occur. (This is part of my confusion - a SINGLE detail record can only
have 1 PM).
3. Can more than one detail record have the same PM? Sure. On 10/1/06 I can
complete PM-1 and again on 12/5/07 I can do it again.
4. Yes, a single PM casn have many detail records associated with it over
time.

Jeff said:
How many PMs do you have? How many detail records can each PM have? Can
more than one detail record have the same PM?

Forget about your table structure for a moment and describe the real world
situation you are dealing with.

I suspect that you have a one-to-many relationship between PMs and detail
records. A detail record can only have one PM (true or false?), but a PM
can have many detail records (again, T/F?).

Regards

Jeff Boyce
Microsoft Office/Access MVP
I have an equipment maintenance db. Within it there are 3 tables causing me
some confusion and I am requesting some assistance. Here's what I've got;
Click to expand...
[quoted text clipped - 47 lines]
Thank you
Click to expand...
Click to expand...
 
Back
Top