Calculation of dates

[Aurora]

I am using Access 2000

I want to created a report from a machine mtce database
that contains 2 Tables. One table is for basic machine
information by machine #, the 2nd table includes the
maintenance completed on each machine by machine#. In the
2nd table I have a mtce completion date and I have a
number field to state how often the mtce needs to be
completed - Ex 30 days, 90 days, 180 dates etc.

I want to calculate when the next maintenance is due. I
can do that formula by adding the days to the last
maintenance completed date. But how do I get the report
to only pick up the latest due date that was not
completed. I thought I could query on "> than todays
date", but what would happen if the previous maintenance
schedule was not completed and a machine was due for
maintenance last month or 2 months ago and was not done.

Does anyone have any ideas?????????

Aurora

 

[Allen Browne]

Create a query into your "Machine" table.

Enter something like this into the Field row of the query:
Next Due: CVDate(DateAdd("d", [Days],
( SELECT Max([completed]) AS LastDone FROM [maintenance]
WHERE [maintenance].[machine#] = [machine].[machine#] )))

The calculated field gets the maximum value of the field named "completed"
in the table named "maintenance" where the machine# is the same as the
record in the main query. It then adds the number of days from the field
named "Days" in the main table.

The whole expression is then wrapped in CVDate() because Access doesn't
always understand that the result of the calculation is a date, especially
where some fields are Null.

The resultant query is read-only. If you need an updatable query, you can
use DMax() rather than the subquery, but that will be *way* slower.

 

[Aurora]

Allen - Thank you for replying to my inquiry. I put the
formula into the query, and in the query screen it worked
fine. But as you said, I can not put it into a report
and this I have to do inorder for the employees to know
what maintenance is due the next week. When I tried to
put it into my report I got the following message. "Multi-
level GROUP By Clause is not allowed in a sub query."
What does this mean??? Any other suggestions??

Aurora

-----Original Message-----
Create a query into your "Machine" table.

Enter something like this into the Field row of the query:
Next Due: CVDate(DateAdd("d", [Days],
( SELECT Max([completed]) AS LastDone FROM [maintenance]
WHERE [maintenance].[machine#] = [machine]. [machine#] )))

The calculated field gets the maximum value of the field named "completed"
in the table named "maintenance" where the machine# is the same as the
record in the main query. It then adds the number of days from the field
named "Days" in the main table.

The whole expression is then wrapped in CVDate() because Access doesn't
always understand that the result of the calculation is a date, especially
where some fields are Null.

The resultant query is read-only. If you need an updatable query, you can
use DMax() rather than the subquery, but that will be *way* slower.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 2000

I want to created a report from a machine mtce database
that contains 2 Tables. One table is for basic machine
information by machine #, the 2nd table includes the
maintenance completed on each machine by machine#. In the
2nd table I have a mtce completion date and I have a
number field to state how often the mtce needs to be
completed - Ex 30 days, 90 days, 180 dates etc.

I want to calculate when the next maintenance is due. I
can do that formula by adding the days to the last
maintenance completed date. But how do I get the report
to only pick up the latest due date that was not
completed. I thought I could query on "> than todays
date", but what would happen if the previous maintenance
schedule was not completed and a machine was due for
maintenance last month or 2 months ago and was not done.

Does anyone have any ideas?????????

Aurora

.

 

[Allen Browne]

Rats. I hate getting that error at the report level after solving it in the
query. It means that Access cannot perform whatever you asked for the the
report's Sorting'n'Grouping box because the calculated field is not
available at the time it tries to do it.

One way around that is to use one query stacked on another:
1. Create a query into your maintenance table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.

3. Drag [machine#] into the grid.
Accept "Group By" in the Total row.

4. Drag [completed] into the grid.
In the Total row under this field, choose Max.

5. Save the query. Close.

6. Create another query based that has the first query as a source "table"
as well as your machine table. If you do not see a line from machine# in the
query to machine# in the table, create it by dragging machine# from one onto
the other.

7. Enter the calculated field into a fresh column (Field row) of the query
design grid:
NextDue: CVDate(DateAdd("d", [Days], [MaxOfCompleted]))

8. Save this query, and use it as the source for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen - Thank you for replying to my inquiry. I put the
formula into the query, and in the query screen it worked
fine. But as you said, I can not put it into a report
and this I have to do inorder for the employees to know
what maintenance is due the next week. When I tried to
put it into my report I got the following message. "Multi-
level GROUP By Clause is not allowed in a sub query."
What does this mean??? Any other suggestions??

Aurora

-----Original Message-----
Create a query into your "Machine" table.

Enter something like this into the Field row of the query:
Next Due: CVDate(DateAdd("d", [Days],
( SELECT Max([completed]) AS LastDone FROM [maintenance]
WHERE [maintenance].[machine#] = [machine]. [machine#] )))

The calculated field gets the maximum value of the field named "completed"
in the table named "maintenance" where the machine# is the same as the
record in the main query. It then adds the number of days from the field
named "Days" in the main table.

The whole expression is then wrapped in CVDate() because Access doesn't
always understand that the result of the calculation is a date, especially
where some fields are Null.

The resultant query is read-only. If you need an updatable query, you can
use DMax() rather than the subquery, but that will be *way* slower.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 2000

I want to created a report from a machine mtce database
that contains 2 Tables. One table is for basic machine
information by machine #, the 2nd table includes the
maintenance completed on each machine by machine#. In the
2nd table I have a mtce completion date and I have a
number field to state how often the mtce needs to be
completed - Ex 30 days, 90 days, 180 dates etc.

I want to calculate when the next maintenance is due. I
can do that formula by adding the days to the last
maintenance completed date. But how do I get the report
to only pick up the latest due date that was not
completed. I thought I could query on "> than todays
date", but what would happen if the previous maintenance
schedule was not completed and a machine was due for
maintenance last month or 2 months ago and was not done.

Does anyone have any ideas?????????

Aurora

.