total group & max function

[Aurora]

I am using Access 2003
I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic
Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists
of different prev mtce (PM) that need to be completed at various time frames.
Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the
oil due every 90 days, Oper # 100B to check the belts due every 180 days,
Oper # 100C to grease the machine due every 365 days.
The 3rd table is the "PM History" table which includes the history of all of
the PM's completed with Work Order (WO#) and date completed. All tables are
linked by Mach #.
I am trying to create a report that will look at the latest date a PM was
done (from the pm history table) for a Mach# and by Oper#. Then to that date
add the [freq] from the Operations table to come up with a NextPMDue date.
Ex: Last pm, for Mach 100, Oper 100-B, was completed on 1/5/06 and due again
in 180 days. Therefore the new pm due date would be 7/5/06. I think this
takes the use of the
"max" function and probably the " total" group by function. I am now very
good at using either of these functions.

Is there anyplace on the internet that could help explain to me how to
accomplish this or to use these functions. I do not know how to write SQL or
VBasic. I would appreciate any help you can give me.

aurora

 

[KARL DEWEY]

Make a PM Records table like this ---
Mach# Operations Sched Complete Comments
1 A 1/2/2007 1/3/2007 No problems
1 B 1/2/2007 1/3/2007 No problems
1 C 1/2/2007 1/3/2007 No problems
1 C 1/3/2008 1/8/2008
1 C 1/8/2009
Create an index of the first three fields set to unique.

The Operations Table to have the following ---
Mach Operations Interval Increment Activity
1 A 90 d change the oil
1 B 180 d check the belts
1 C 1 yyyy grease the machine
The increment is used in DateAdd function with interval for the next
scheduled date.

Use these two queries after you have a PM record for every Mach/Operation
combination. Set the record dates so as to generate the next PM on your
desired dates.
Mach PM Record MAX ---
SELECT [Basic Mach Infor].[Mach#], [Mach PM Records].Operations, Max([Mach
PM Records].Complete) AS MaxOfComplete
FROM [Basic Mach Infor] LEFT JOIN [Mach PM Records] ON [Basic Mach
Infor].[Mach#] = [Mach PM Records].[Mach#]
GROUP BY [Basic Mach Infor].[Mach#], [Mach PM Records].Operations;
This query select the last complete date but you may wish to use the last
schedule date.

Mach PM Record Gen ---
INSERT INTO [Mach PM Records] ( [Mach#], Operations, Sched )
SELECT [Mach PM Record MAX].[Mach#], [Operations Table].Operations,
DateAdd([Increment],[Interval],[MaxOfComplete]) AS x
FROM ([Mach PM Record MAX] LEFT JOIN [Operations Table] ON ([Mach PM Record
MAX].[Mach#] = [Operations Table].Mach) AND ([Mach PM Record MAX].Operations
= [Operations Table].Operations)) INNER JOIN [Mach PM Records] ON ([Mach PM
Record MAX].[Mach#] = [Mach PM Records].[Mach#]) AND ([Mach PM Record
MAX].Operations = [Mach PM Records].Operations)
WHERE ((([Mach PM Records].Complete) Is Not Null))
GROUP BY [Mach PM Record MAX].[Mach#], [Operations Table].Operations,
DateAdd([Increment],[Interval],[MaxOfComplete]);

--
KARL DEWEY
Build a little - Test a little

I am using Access 2003
I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic
Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists
of different prev mtce (PM) that need to be completed at various time frames.
Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the
oil due every 90 days, Oper # 100B to check the belts due every 180 days,
Oper # 100C to grease the machine due every 365 days.
The 3rd table is the "PM History" table which includes the history of all of
the PM's completed with Work Order (WO#) and date completed. All tables are
linked by Mach #.
I am trying to create a report that will look at the latest date a PM was
done (from the pm history table) for a Mach# and by Oper#. Then to that date
add the [freq] from the Operations table to come up with a NextPMDue date.
Ex: Last pm, for Mach 100, Oper 100-B, was completed on 1/5/06 and due again
in 180 days. Therefore the new pm due date would be 7/5/06. I think this
takes the use of the
"max" function and probably the " total" group by function. I am now very
good at using either of these functions.

Is there anyplace on the internet that could help explain to me how to
accomplish this or to use these functions. I do not know how to write SQL or
VBasic. I would appreciate any help you can give me.

aurora