Find the latest date

[Guest]

I am using Access 2003
I am developing a Preventative Mtce program for our plant. The db is built
on Machine # and Operation #. Ex:
Mach # Oper# Date Completed
150 150-A (Check Oil) 10/1/06
150 150-B (Check Belts) 5/1/06
150 150-C (Do something else) 8/1/05
140 140-A (check oil) 9/1/06

Note: The operation # contains the machine # and a letter A, B, C etc. The
letter signifies different prev mtce (pm) items that need to be done on a
particular machine and usually at different intervals (30, 60, 90 days etc.
Some items are only done annually). A lot of the machine only have 1 or 2
items that are included in PM, other machines may have up to 6 items that
need to be completed. So as time goes by each machine history will show the
same item (ex 150A) being completed several time over the year(s).

I am trying to develop a report that will tell the mechanic when the next PM
is due. I am looking at a calculated field that would take the "last date"
an operation was completed for each machine, and add the frequency (30, 60,
90 Days etc) to come up with a "Next PM Due Date". But I do not know how to
indicate only the "last date" of an operation.

In a previous request - someone suggested a query using "totals" and the max
value in the [date completed] field. I tried this but I must be doing
something wrong because it did not appear to work. It pulled all the records
just as it had done before. Can someone point me in the right direction to
solve this problem.

Aurora

 

[Ken Snell \(MVP\)]

You need to use a subquery in the WHERE clause:

SELECT T.[Mach #], T.[Oper#],
([DateAdd("d", 30, T.[Date Completed])) AS NextMaintDate
FROM YourTableName AS T
WHERE T.[Date Completed] =
(SELECT Max(Q.[Date Completed])
FROM YourTableName AS Q
WHERE Q.[Mach #] = T.[Mach #] And
Q.[Oper#] = T.[Oper#]);

In the above example, I've assumed 30 days for the maintenance interval,
even though you said the interval will be different for the operations. Do
you store the interval values in a table? Or how do you know what interval
is to be used? Post back with this info and we'll show you how to
incorporate that logic into the query too.

 

[Guest]

I have the following fields; [WO#](auto number assigned to new Work Orders
for PM's due), [mach #], [Operation#] (same as the machine number except it
has a letter (A,B,C etc) added to the number. Each letter indicates a
different type of PM due such as oil change, belt change, motor check
etc),[freq] (# of days before this PM has to done again. ex: 30,60,90, 365
days etc), [WO Date] (date the PM was completed for each operation (record),
and a [Next PM Date due] (this is a calculated field that takes the [WODate]
and adds [freq] = [Next PM Date due].
I created the query, with the "total" row and put "max" in the WO Date field.
Now what do I do?

Aurora

You need to use a subquery in the WHERE clause:

SELECT T.[Mach #], T.[Oper#],
([DateAdd("d", 30, T.[Date Completed])) AS NextMaintDate
FROM YourTableName AS T
WHERE T.[Date Completed] =
(SELECT Max(Q.[Date Completed])
FROM YourTableName AS Q
WHERE Q.[Mach #] = T.[Mach #] And
Q.[Oper#] = T.[Oper#]);

In the above example, I've assumed 30 days for the maintenance interval,
even though you said the interval will be different for the operations. Do
you store the interval values in a table? Or how do you know what interval
is to be used? Post back with this info and we'll show you how to
incorporate that logic into the query too.
--

Ken Snell
<MS ACCESS MVP>

I am using Access 2003
I am developing a Preventative Mtce program for our plant. The db is built
on Machine # and Operation #. Ex:
Mach # Oper# Date Completed
150 150-A (Check Oil) 10/1/06
150 150-B (Check Belts) 5/1/06
150 150-C (Do something else) 8/1/05
140 140-A (check oil) 9/1/06

Note: The operation # contains the machine # and a letter A, B, C etc. The
letter signifies different prev mtce (pm) items that need to be done on a
particular machine and usually at different intervals (30, 60, 90 days
etc.
Some items are only done annually). A lot of the machine only have 1 or 2
items that are included in PM, other machines may have up to 6 items that
need to be completed. So as time goes by each machine history will show
the
same item (ex 150A) being completed several time over the year(s).

I am trying to develop a report that will tell the mechanic when the next
PM
is due. I am looking at a calculated field that would take the "last date"
an operation was completed for each machine, and add the frequency (30,
60,
90 Days etc) to come up with a "Next PM Due Date". But I do not know how
to
indicate only the "last date" of an operation.

In a previous request - someone suggested a query using "totals" and the
max
value in the [date completed] field. I tried this but I must be doing
something wrong because it did not appear to work. It pulled all the
records
just as it had done before. Can someone point me in the right direction
to
solve this problem.

Aurora

 

[Ken Snell \(MVP\)]

So you have a field that is storing for each record the number of days until
the next date for performing that function? A bit unnormalized structure for
your table, but, ok....

We'd change the query to this:

SELECT T.[Mach #], T.[Operation#] ],
T.[Next PM Date due] AS NextMaintDate
FROM YourTableName AS T
WHERE T.[WO Date] =
(SELECT Max(Q.[WO Date])
FROM YourTableName AS Q
WHERE Q.[Mach #] = T.[Mach #] And
Q.[Operation#] ] = T.[Operation#] ]);

As for how to create the query, we won't use the grid for right now. Create
a new query, select no tables (close the initial popup window), then click
on View icon (usually at far left of toolbar) and select the SQL View
(should be showing as the default).

When the next window opens, paste the above SQL statement into the window.
Change YourTableName (it appears twice in the above query) to the actual
table or query name from which you're getting your data.

Now change to design view and you'll be able to see how the query is
structured on the grid.
--

Ken Snell
<MS ACCESS MVP>

I have the following fields; [WO#](auto number assigned to new Work Orders
for PM's due), [mach #], [Operation#] (same as the machine number except
it
has a letter (A,B,C etc) added to the number. Each letter indicates a
different type of PM due such as oil change, belt change, motor check
etc),[freq] (# of days before this PM has to done again. ex: 30,60,90,
365
days etc), [WO Date] (date the PM was completed for each operation
(record),
and a [Next PM Date due] (this is a calculated field that takes the
[WODate]
and adds [freq] = [Next PM Date due].
I created the query, with the "total" row and put "max" in the WO Date
field.
Now what do I do?

Aurora

You need to use a subquery in the WHERE clause:

SELECT T.[Mach #], T.[Oper#],
([DateAdd("d", 30, T.[Date Completed])) AS NextMaintDate
FROM YourTableName AS T
WHERE T.[Date Completed] =
(SELECT Max(Q.[Date Completed])
FROM YourTableName AS Q
WHERE Q.[Mach #] = T.[Mach #] And
Q.[Oper#] = T.[Oper#]);

In the above example, I've assumed 30 days for the maintenance interval,
even though you said the interval will be different for the operations.
Do
you store the interval values in a table? Or how do you know what
interval
is to be used? Post back with this info and we'll show you how to
incorporate that logic into the query too.
--

Ken Snell
<MS ACCESS MVP>

I am using Access 2003
I am developing a Preventative Mtce program for our plant. The db is
built
on Machine # and Operation #. Ex:
Mach # Oper# Date Completed
150 150-A (Check Oil) 10/1/06
150 150-B (Check Belts) 5/1/06
150 150-C (Do something else) 8/1/05
140 140-A (check oil) 9/1/06

Note: The operation # contains the machine # and a letter A, B, C etc.
The
letter signifies different prev mtce (pm) items that need to be done on
a
particular machine and usually at different intervals (30, 60, 90 days
etc.
Some items are only done annually). A lot of the machine only have 1 or
2
items that are included in PM, other machines may have up to 6 items
that
need to be completed. So as time goes by each machine history will show
the
same item (ex 150A) being completed several time over the year(s).

I am trying to develop a report that will tell the mechanic when the
next
PM
is due. I am looking at a calculated field that would take the "last
date"
an operation was completed for each machine, and add the frequency (30,
60,
90 Days etc) to come up with a "Next PM Due Date". But I do not know
how
to
indicate only the "last date" of an operation.

In a previous request - someone suggested a query using "totals" and
the
max
value in the [date completed] field. I tried this but I must be doing
something wrong because it did not appear to work. It pulled all the
records
just as it had done before. Can someone point me in the right
direction
to
solve this problem.

Aurora