Easy Question, Can't see Answer

[DissentChick]

Ok, I'm having a little trouble seeing what I'm sure is a very simple
solution, and it's driving me nuts. I need to make some tables and
relationships in my db that will allow me to associate one piece of equipment
to multiple Frequencies that have multiple ServiceDetails associated with
Frequencies- Example- FAN 210 is due for a Quarterly service, so I want it to
pull up the Quarterly serviceDetails items for Fans. But, next month Fan 210
is due for an Annual full service, and I want to know that, too, but it has
different Details. COuld anyone help me with this? I would be so grateful-
I'm thinking I need a junction table for Frequencies to Equipment and a
junction table from Frequencies to Details? Thanks!

 

[Sharkbyte]

You are quite right, on both accounts.

You want to have a relationship from Equipment to Frequencies to Details.
(In a previous life I dealt with this under the terms Asset/PM/Task. =) )

The basic table/fields would be something like this:

tblEquipment
EquipmentID (PK)

tblFrequencies
FrequencyID (PK)

tblEquipFreq
FrequencyID (PK) (FK)
EquipmentID (PK) (FK)

tblDetails
DetailID (PK)
FrequencyID (FK)


HTH

Sharkbyte

 

[DissentChick]

Oooh, thank you so much! Would you possibly be interested in sharing any
other tips you may have about PM schedules in Access? The whole thing has
given us a fit, and I can't ever seem to get it right- I would love for it to
run off of a date, like a LastPMDate field....but if no, I understand- thanks
so much! Aaron

 

[Sharkbyte]

Aaron:

Probably the easiest way to use dates would be to put a begin date, and a
frequency field (let's say in terms of days), and NextDueDate field in the
suggested tblEquipFreq.

Create a form to view the current PMs, and when someone verifies the PM as
completed, calculate [NextDueDate] + [Frequecy] and update NextDueDate with
the result. If NextDueDate isnull (first update) use [BeginDate] +
[Frequency].

You also have the option of going from the CompletedDate. Your formula
would look something like [Date()] + [Frequency].

Good Luck

Sharkbyte

 

[DissentChick]

Thanks so much for your help- I should be able to do it this way, you rock!
--
Aaron Regular
Dissent Records
www.nbtnc.com

Aaron:

Probably the easiest way to use dates would be to put a begin date, and a
frequency field (let's say in terms of days), and NextDueDate field in the
suggested tblEquipFreq.

Create a form to view the current PMs, and when someone verifies the PM as
completed, calculate [NextDueDate] + [Frequecy] and update NextDueDate with
the result. If NextDueDate isnull (first update) use [BeginDate] +
[Frequency].

You also have the option of going from the CompletedDate. Your formula
would look something like [Date()] + [Frequency].

Good Luck

Sharkbyte

Oooh, thank you so much! Would you possibly be interested in sharing any
other tips you may have about PM schedules in Access? The whole thing has
given us a fit, and I can't ever seem to get it right- I would love for it to
run off of a date, like a LastPMDate field....but if no, I understand- thanks
so much! Aaron