How do I count Uses

[CAT]

I have built an access data base for Preventive
maintenance.
My problem is this. Some machines need to certain tasks
performed after 5 uses, 30 uses, some daily, monthly, etc.
But
not on cesecutive days..some may be used once a week or
once every other day.
For now I am trying to figure out the 5 use part. I have
them log in each time the machine is used and an ondate is
entered. What I can't get is how to count each time the
machine has been turned on 5 times and needs a task
preformed and some how flags the operator the task is due.
Then resets itself to count the next 5 uses, etc.
Any help I could get would be very much appreciated.
What I would like to do is when the PM No. and Ondate is
entered it would pull up the tasks due for that day.
My Tables are as follows:
tbl Machine Onday tbl Machine ID Tasks Table
Machine OndayID Machine ID Task ID
Machine ID PM No Machine ID
Onday Date Equipment Name Task Notes
Location
Property Tag
Serial No
Rev Level
Notes

There are more tables but they have to do with other
functions

..

 

[Allen Browne]

There are several steps to achieve this.

Presumably you already have these tables:
- tblMachine (one record for each machine), with MachineID as primary key;

- tblUse (one record each time a machine is used), with UseDate, and
MachineID as foreign key;

- tblService (one record each time a machine is serviced), with ServiceDate,
and MachineID as foreign key.

To create a query showing the last service date of each machine:
SELECT MachineID, MachineName,
(SELECT Max(ServiceDate) AS LastServiced
FROM tblService
WHERE tblService.MachineID = tblMachine.MachineID) AS LastServiced
FROM tblMachine;

To create a query showing the number of uses since the last service date for
each machine:
SELECT MachineID, MachineName,
(SELECT Count(UseID) AS CountOfUses
FROM tblUse
WHERE (tblMachine.MachineID = tblUse.MachineID) AND (tblUse.UseDate >
(SELECT Max(ServiceDate) AS LastServiced
FROM tblService
WHERE tblService.MachineID = tblUse.MachineID)))
FROM tblMachine;

 

[Allen Browne]

Hi CAT.

Thanks for asking (rather than just sending), but the aim of these
newsgroups is to help you to help yourself. We cannot examine everyone's
databases unless we charge for that.

 

[tina]

try counting the number of times the machine has been logged on (from your
table). one way you can do this is by using the DCount function.

divide that number by the "number at which a task should be performed" such
as the 5 used in your post. use the mod operator, which will give you the
remainder, as

28 mod 5 = 3

28 being the count from the table, 5 being the number at which a task should
be performed, and 3 is the remainder of 28 divided by 5.

when the remainder of the mod operation is zero (0), as in

25 mod 5 = 0

then it's time to do the task.

hth