Need Help BAD

[Katzy]

I have built a database for keeping history of maintenance
of machine on our plant. It includes all data about the
specific machines (Identified by PM Numbers) Daily
Maintenance records are not a prob..just enter date
complete, also annual or semi annual as they have an
assigned due date. BUT there are some machine that only
need manintenance every 5 Uses or 30 Uses...this is where
I find myself in trouble.
Could someone suggest how I keep track of Uses before a
complete date is needed.
Please ber gentle I am a novice at this.

Kat

 

[Tim Ferguson]

Could someone suggest how I keep track of Uses before a
complete date is needed.

You have a table of Uses, and then a query you could run daily that
identifies machines with a criterion of something like the having clause
here:-

SELECT Machines.PMNumber,
Machines.UsesBeforeService,
Count(Uses.*) AS CountOfUses

FROM Machines LEFT JOIN Uses
ON Machines.PMNumber = Uses.PMNumber

GROUP BY Machines.PMNumber

HAVING Count(Uses.*) >= Machines.UsesBeforeService

WHERE Machines.UsesBeforeService > 0

You need a row in the Uses table each time one of the machines is used; and
if the service is not dependent on the number of uses, then mark the
requirement as zero.

Hope that helps


Tim F

 

[Guest]

Thanks Tim, I will try that.

-----Original Message-----


You have a table of Uses, and then a query you could run daily that
identifies machines with a criterion of something like the having clause
here:-

SELECT Machines.PMNumber,
Machines.UsesBeforeService,
Count(Uses.*) AS CountOfUses

FROM Machines LEFT JOIN Uses
ON Machines.PMNumber = Uses.PMNumber

GROUP BY Machines.PMNumber

HAVING Count(Uses.*) >= Machines.UsesBeforeService

WHERE Machines.UsesBeforeService > 0

You need a row in the Uses table each time one of the machines is used; and
if the service is not dependent on the number of uses, then mark the
requirement as zero.

Hope that helps


Tim F

.