HELP with counting Querie PLEASE

[Kathy]

I have built a 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.
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 appreiated.
...

 

[Mark]

Kathy,

Ignoring the logging problem of someone (or a routine) to
record the machine being turned on, here's what I would
design

Table: tblMachines
Field: MachineID (long or autonumber)
Machine (text)

Table: tblMachineOn
Field: MachineID (long)
MachineOn (date)

Table: tblMaintenanceTypes
Field: MaintenanceTypeID (long or autonumber)
MaintenanceType (text)
MaintenanceDays (integer)

Query 1: qryMaintenance01

SELECT MachineID, Count(MachineOn) AS TimesUsed
FROM tblMachineOn GROUP BY MachineID;

Query 2: qryMaintenance

SELECT tblMaintenanceTypes.MaintenanceType,
qryMaintenance01.MachineID, tblMachines.Machine
FROM tblMaintenanceTypes, qryMaintenance01 INNER JOIN
tblMachines ON qryMaintenance01.MachineID =
tblMachines.MachineID
WHERE ((([TimesUsed] Mod [MaintenanceTimes])=0));

You should be able to get a report based on that. I hope
that helps.

Mark

 

[MGFoster]

I have built a 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.
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 appreiated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you'd be satisfied w/ how many times the application was used you
could do this:

1. Create a table like this:

CREATE TABLE Uses ( Used INTEGER NOT NULL )

2. When the application starts increment the Used column in the Uses
table. If the result = 5 (or, whatever number you want) then pop-up a
message that tells the user what to do. Then reset the Uses value to 0.

For time intervals, instead of use intervals, you'd have to have a
StartDate, instead of a Used value, to compare to the Date().

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIAl7YechKqOuFEgEQKW4QCgsE8XcPalmd7F6hLwxDKEksWa6Q4AoLz6
h1GcZ4vtHtlRmLLyQPWVLcqA
=h/wM
-----END PGP SIGNATURE-----

 

[Kathy]

Your info is helpful but I am trying to get the data into
a form not a report. I have a number of tables and forms
and subforms. I would like some one to look at my dbase
and let me know what they think. Would you be interested
in looking at it....I am desperate.
Thanks,
Kathy

-----Original Message-----
Kathy,

Ignoring the logging problem of someone (or a routine) to
record the machine being turned on, here's what I would
design

Table: tblMachines
Field: MachineID (long or autonumber)
Machine (text)

Table: tblMachineOn
Field: MachineID (long)
MachineOn (date)

Table: tblMaintenanceTypes
Field: MaintenanceTypeID (long or autonumber)
MaintenanceType (text)
MaintenanceDays (integer)

Query 1: qryMaintenance01

SELECT MachineID, Count(MachineOn) AS TimesUsed
FROM tblMachineOn GROUP BY MachineID;

Query 2: qryMaintenance

SELECT tblMaintenanceTypes.MaintenanceType,
qryMaintenance01.MachineID, tblMachines.Machine
FROM tblMaintenanceTypes, qryMaintenance01 INNER JOIN
tblMachines ON qryMaintenance01.MachineID =
tblMachines.MachineID
WHERE ((([TimesUsed] Mod [MaintenanceTimes])=0));

You should be able to get a report based on that. I hope
that helps.

Mark

-----Original Message-----
I have built a 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.
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 appreiated.
...


.

.

 

[kathy]

Your info is helpful but I am trying to get the data into
a form not a report. I have a number of tables and forms
and subforms. I would like some one to look at my dbase
and let me know what they think. Would you be interested
in looking at it....I am desperate.
Thanks,
Kathy