Looking for LATEST DATE

[Aurora]

I am using Access 2003
I have a machine maintenance Db. This is a one to many db. One machine
number and many operations and maintenance dates. I need to create a field
that looks for the last maintenance date and adds the frequency in days for
each operation to come up with the next maintenance date that is due. Ex:
Machine #100 had maintenance done on Jan 3, 2006, July 5, 2006, and again Jan
4, 2007. (180 day intervals). Each date would be a different record for the
same machine #100. How can I have Access find the latest date (jan 4, 2007)
add 180 days and tell me that the next maintenance is due July, 2007. I
would certainly appreciate any help someone can give me.

Thank You - Aurora

 

[Michael Gramelspacher]

I am using Access 2003
I have a machine maintenance Db. This is a one to many db. One machine
number and many operations and maintenance dates. I need to create a field
that looks for the last maintenance date and adds the frequency in days for
each operation to come up with the next maintenance date that is due. Ex:
Machine #100 had maintenance done on Jan 3, 2006, July 5, 2006, and again Jan
4, 2007. (180 day intervals). Each date would be a different record for the
same machine #100. How can I have Access find the latest date (jan 4, 2007)
add 180 days and tell me that the next maintenance is due July, 2007. I
would certainly appreciate any help someone can give me.

Thank You - Aurora

maybe:

SELECT M.machine_no,
M.maintenance_internal_days,
M.[Last Maintenance],
DATEADD("d",maintenance_internal_days,[Last Maintenance]) AS [Next
Maintenance]
FROM (SELECT M.machine_no,
M.maintenance_internal_days,
MAX(M.maintenance_date) AS [Last Maintenance]
FROM MaintenanceSchedules AS M
GROUP BY M.machine_no,M.maintenance_internal_days) AS M;

 

[Aurora]

Michael:
I am sorry, but I do not understand what you are telling me. Is this a
formula for the query? Is this VB? I do not know how to do write or
interpret VB.
Is there somewhere at MS that I can read up on your formula?

Aurora

I am using Access 2003
I have a machine maintenance Db. This is a one to many db. One machine
number and many operations and maintenance dates. I need to create a field
that looks for the last maintenance date and adds the frequency in days for
each operation to come up with the next maintenance date that is due. Ex:
Machine #100 had maintenance done on Jan 3, 2006, July 5, 2006, and again Jan
4, 2007. (180 day intervals). Each date would be a different record for the
same machine #100. How can I have Access find the latest date (jan 4, 2007)
add 180 days and tell me that the next maintenance is due July, 2007. I
would certainly appreciate any help someone can give me.

Thank You - Aurora

maybe:

SELECT M.machine_no,
M.maintenance_internal_days,
M.[Last Maintenance],
DATEADD("d",maintenance_internal_days,[Last Maintenance]) AS [Next
Maintenance]
FROM (SELECT M.machine_no,
M.maintenance_internal_days,
MAX(M.maintenance_date) AS [Last Maintenance]
FROM MaintenanceSchedules AS M
GROUP BY M.machine_no,M.maintenance_internal_days) AS M;

 

[J_Goddard via AccessMonster.com]

Hi -

I understand what it is you are looking for, but what do you want to do with
the data?

You said "I need to create a field that looks for the last maintenance date..
..", but fields are for storing and/or displaying data - they don't "do"
anything.

Having said that, I think what you need is a totals query, with data from two
tables "Machine" and "MaintenanceRecords". The relationship between the
tables would be defined using the MachineNumber (or whatever you use to
identify a machine).

The query would GROUP BY machine number; have a field which displayed the
latest (maximum) maintenance data from the Maintenance table; and a
calculated (expression) field which added the maintenance frequency to the
maximum date, to give the next scheduled maintenance date.

The SQL for the query might look something like this:

SELECT [Machine].Machine_ID, [Machine].Description, [Machine].MaintInterval,
Max([MaintenanceRecords].MaintenanceDate) AS Last_Maintenance,
Max([MaintenanceRecords].MaintenanceDate) +
DateAdd("d", [Machine].MaintInterval, [Last_Maintenance]) AS
Next_Maintenance
FROM [Machine] INNER JOIN [MaintenanceRecords]
ON [Machine].Machine_ID = [MaintenanceRecords].Machine_ID
GROUP BY [Machine].Machine_ID, [Machine].Description,[Machine].MaintInterval;

You can use the query design grid to design the query, without having to
write the SQL yourself, and
you would of course have to change the field and table names to match yours.

This will give you the "Next Maintenance" data you need.

John

 

[Michael Gramelspacher]

Michael:
I am sorry, but I do not understand what you are telling me. Is this a
formula for the query? Is this VB? I do not know how to do write or
interpret VB.
Is there somewhere at MS that I can read up on your formula?

Aurora

I am using Access 2003
I have a machine maintenance Db. This is a one to many db. One machine
number and many operations and maintenance dates. I need to create a field
that looks for the last maintenance date and adds the frequency in days for
each operation to come up with the next maintenance date that is due. Ex:
Machine #100 had maintenance done on Jan 3, 2006, July 5, 2006, and again Jan
4, 2007. (180 day intervals). Each date would be a different record for the
same machine #100. How can I have Access find the latest date (jan 4, 2007)
add 180 days and tell me that the next maintenance is due July, 2007. I
would certainly appreciate any help someone can give me.

Thank You - Aurora

maybe:

SELECT M.machine_no,
M.maintenance_internal_days,
M.[Last Maintenance],
DATEADD("d",maintenance_internal_days,[Last Maintenance]) AS [Next
Maintenance]
FROM (SELECT M.machine_no,
M.maintenance_internal_days,
MAX(M.maintenance_date) AS [Last Maintenance]
FROM MaintenanceSchedules AS M
GROUP BY M.machine_no,M.maintenance_internal_days) AS M;

You posted in the queries newsgroup. I assumed you wanted a query
solution. This is a query in SQL. The query is based on an example
table, which I thought would be similar to your table. There is
prerequisite knowledge to understanding the solution. I do not think I
can help you in that area.

 

[John W. Vinson]

Michael:
I am sorry, but I do not understand what you are telling me. Is this a
formula for the query? Is this VB? I do not know how to do write or
interpret VB.
Is there somewhere at MS that I can read up on your formula?

PMFJI....

THis is a Query. The native language of Access Queries is SQL; the query grid
is simply a tool for creating SQL queries.

Create a new Query based on your table. Select View... SQL from the menu (you
should see just the single word SELECT; in the window). Copy and paste
Michael's query into the window, overwriting it. Change the table names and
field names to the names of your table and your fields.

John W. Vinson [MVP]

 

[Aurora]

John:
I have already done something similar to what you have suggested, but it is
not working.
Let me try to describe in more detail what I have done. For this database,
I have 3 tables. (1) – “BasicMach†information (name, number, mfg, active
etc). (2) “Operations†by [mach#] and operation # [oper #] including the
frequency [freq] of each operation. Ex: Mach #125, could have several
operations - #125-A, chg oil every 30 days; #125-B, chg belt every 180 days;
#125-C, drain tank every 90 days etc. (3) “PMHistoryâ€. This table records
all of the PM’s completed by date [jobdate] and work order number [WOnumber]
assigned. All 3 tables are linked by Mach#.

My query uses all 3 tables to try to find which PM’s are due this month, I
have the following fields:
NextPM: Max([jobdate]+[freq]) (Total row = Expression)
Mach# (Total row = Group By)
Oper# (Total row = Group By)
JobDate (Total row = Group By)
Freq (Total row = Group By)

This query is not finding the next pm due date by operation. I think it is
looking for the last date per machine. Do you have any idea as to how to
remedy this?

Since I do not really understand SQL – I copied what the SQL in this query
says.

SELECT [Tbl-PMHistory].JOBDATE, [Tbl-Operations].Freq, Max([jobdate]+[freq])
AS NextPM, [Tbl-PMHistory].[Mach#], [Tbl-Operations].[Oper #],
[Tbl-BasicMachines].Active
FROM ([Tbl-BasicMachines] INNER JOIN [Tbl-Operations] ON
[Tbl-BasicMachines].[Mach#] = [Tbl-Operations].[Mach#]) INNER JOIN
[Tbl-PMHistory] ON [Tbl-BasicMachines].[Mach#] = [Tbl-PMHistory].[Mach#]
GROUP BY [Tbl-PMHistory].JOBDATE, [Tbl-Operations].Freq,
[Tbl-PMHistory].[Mach#], [Tbl-Operations].[Oper #], [Tbl-BasicMachines].Active
ORDER BY Max([jobdate]+[freq]);

Is there anything you can suggest to make this work for me?

Aurora

Hi -

I understand what it is you are looking for, but what do you want to do with
the data?

You said "I need to create a field that looks for the last maintenance date..
..", but fields are for storing and/or displaying data - they don't "do"
anything.

Having said that, I think what you need is a totals query, with data from two
tables "Machine" and "MaintenanceRecords". The relationship between the
tables would be defined using the MachineNumber (or whatever you use to
identify a machine).

The query would GROUP BY machine number; have a field which displayed the
latest (maximum) maintenance data from the Maintenance table; and a
calculated (expression) field which added the maintenance frequency to the
maximum date, to give the next scheduled maintenance date.

The SQL for the query might look something like this:

SELECT [Machine].Machine_ID, [Machine].Description, [Machine].MaintInterval,
Max([MaintenanceRecords].MaintenanceDate) AS Last_Maintenance,
Max([MaintenanceRecords].MaintenanceDate) +
DateAdd("d", [Machine].MaintInterval, [Last_Maintenance]) AS
Next_Maintenance
FROM [Machine] INNER JOIN [MaintenanceRecords]
ON [Machine].Machine_ID = [MaintenanceRecords].Machine_ID
GROUP BY [Machine].Machine_ID, [Machine].Description,[Machine].MaintInterval;

You can use the query design grid to design the query, without having to
write the SQL yourself, and
you would of course have to change the field and table names to match yours.

This will give you the "Next Maintenance" data you need.

John

I am using Access 2003
I have a machine maintenance Db. This is a one to many db. One machine
number and many operations and maintenance dates. I need to create a field
that looks for the last maintenance date and adds the frequency in days for
each operation to come up with the next maintenance date that is due. Ex:
Machine #100 had maintenance done on Jan 3, 2006, July 5, 2006, and again Jan
4, 2007. (180 day intervals). Each date would be a different record for the
same machine #100. How can I have Access find the latest date (jan 4, 2007)
add 180 days and tell me that the next maintenance is due July, 2007. I
would certainly appreciate any help someone can give me.

Thank You - Aurora

 

[Michael Gramelspacher]

SELECT [Tbl-PMHistory].JOBDATE, [Tbl-Operations].Freq, Max([jobdate]+[freq])
AS NextPM, [Tbl-PMHistory].[Mach#], [Tbl-Operations].[Oper #],
[Tbl-BasicMachines].Active
FROM ([Tbl-BasicMachines] INNER JOIN [Tbl-Operations] ON
[Tbl-BasicMachines].[Mach#] = [Tbl-Operations].[Mach#]) INNER JOIN
[Tbl-PMHistory] ON [Tbl-BasicMachines].[Mach#] = [Tbl-PMHistory].[Mach#]
GROUP BY [Tbl-PMHistory].JOBDATE, [Tbl-Operations].Freq,
[Tbl-PMHistory].[Mach#], [Tbl-Operations].[Oper #], [Tbl-BasicMachines].Active
ORDER BY Max([jobdate]+[freq]);

Is there anything you can suggest to make this work for me?

try:

SELECT T.[Mach#],
T.[Oper#],
T.[Last PM],
DATEADD("d",[Tbl-Operations].Freq,[Last PM]) AS [Next PM],
[Tbl-BasicMachines].Active
FROM ((SELECT [Tbl-PMHistory].[Mach#],
[Tbl-PMHistory].[Oper#],
MAX([Tbl-PMHistory].JobDate) AS [Last PM]
FROM [Tbl-PMHistory]
GROUP BY [Tbl-PMHistory].[Mach#],[Tbl-PMHistory].[Oper#]) AS T
INNER JOIN [Tbl-Operations]
ON T.[Oper#] = [Tbl-Operations].[Oper#])
INNER JOIN [Tbl-BasicMachines]
ON T.[Mach#] = [Tbl-BasicMachines].[Mach#];

 

[J_Goddard via AccessMonster.com]

Your query is almost correct -

Try changing it to this:

My query uses all 3 tables to try to find which PM’s are due this month, I
have the following fields:

Mach# (Total row = Group By)
Oper# (Total row = Group By)
JobDate (Total row = Max) <--- change here
Freq (Total row = Group By)
NextPM: Max([jobdate])+[Freq] (Total row = Expression) <--- note change
in expression

John

John:
I have already done something similar to what you have suggested, but it is
not working.
Let me try to describe in more detail what I have done. For this database,
I have 3 tables. (1) – “BasicMach†information (name, number, mfg, active
etc). (2) “Operations†by [mach#] and operation # [oper #] including the
frequency [freq] of each operation. Ex: Mach #125, could have several
operations - #125-A, chg oil every 30 days; #125-B, chg belt every 180 days;
#125-C, drain tank every 90 days etc. (3) “PMHistoryâ€. This table records
all of the PM’s completed by date [jobdate] and work order number [WOnumber]
assigned. All 3 tables are linked by Mach#.

My query uses all 3 tables to try to find which PM’s are due this month, I
have the following fields:
NextPM: Max([jobdate]+[freq]) (Total row = Expression)
Mach# (Total row = Group By)
Oper# (Total row = Group By)
JobDate (Total row = Group By)
Freq (Total row = Group By)

This query is not finding the next pm due date by operation. I think it is
looking for the last date per machine. Do you have any idea as to how to
remedy this?

Since I do not really understand SQL – I copied what the SQL in this query
says.

SELECT [Tbl-PMHistory].JOBDATE, [Tbl-Operations].Freq, Max([jobdate]+[freq])
AS NextPM, [Tbl-PMHistory].[Mach#], [Tbl-Operations].[Oper #],
[Tbl-BasicMachines].Active
FROM ([Tbl-BasicMachines] INNER JOIN [Tbl-Operations] ON
[Tbl-BasicMachines].[Mach#] = [Tbl-Operations].[Mach#]) INNER JOIN
[Tbl-PMHistory] ON [Tbl-BasicMachines].[Mach#] = [Tbl-PMHistory].[Mach#]
GROUP BY [Tbl-PMHistory].JOBDATE, [Tbl-Operations].Freq,
[Tbl-PMHistory].[Mach#], [Tbl-Operations].[Oper #], [Tbl-BasicMachines].Active
ORDER BY Max([jobdate]+[freq]);

Is there anything you can suggest to make this work for me?

Aurora

Hi -

[quoted text clipped - 46 lines]