Expressions using multiple records

[Bob Quintal]

I am tracking production equipment effectiveness. Operators are
selecting the tool designator from a list, entering an event type
and the date/time of the event start. A query pulls out records
for a particular tool, sorted by date/time.

I'd like to add a calculated field that shows event duration, but
that involves using the event start date/time from more than one
record. In other words the event duration is defined by a
record's start date/time and is cut off by the following record's
event start date/time. A simple subtraction if I can pull in the
same field from two different records.

Or do I have to bite the bullet and send it all to Excel?

Not at all. Yhe trick is to use a calculated field in your query
that searches for the min(startdate) which is greater than the
startdate in your current record.

FinishDate: Dmin("startdate", "yourQuery","Startdate > #" &
[startdate] & "# AND [tool designator] = " & [tool designator])

You may need to tweak field names and add some quotemarks if tool
designator is a string.

 

[Bob Quintal]

in

I am tracking production equipment effectiveness. Operators are
selecting the tool designator from a list, entering an event type
and the date/time of the event start. A query pulls out records
for a particular tool, sorted by date/time.

I'd like to add a calculated field that shows event duration, but
that involves using the event start date/time from more than one
record. In other words the event duration is defined by a
record's start date/time and is cut off by the following record's
event start date/time. A simple subtraction if I can pull in the
same field from two different records.

Or do I have to bite the bullet and send it all to Excel?

Not at all. Yhe trick is to use a calculated field in your query
that searches for the min(startdate) which is greater than the
startdate in your current record.

FinishDate: Dmin("startdate", "yourQuery","Startdate > #" &
[startdate] & "# AND [tool designator] = " & [tool designator])

You may need to tweak field names and add some quotemarks if tool
designator is a string.

Forgot to add, then use this column as the source of your
subtraction

 

[D Newcomb]

I am tracking production equipment effectiveness. Operators are selecting the
tool designator from a list, entering an event type and the date/time of the
event start. A query pulls out records for a particular tool, sorted by
date/time.

I'd like to add a calculated field that shows event duration, but that
involves using the event start date/time from more than one record. In other
words the event duration is defined by a record's start date/time and is cut
off by the following record's event start date/time. A simple subtraction if
I can pull in the same field from two different records.

Or do I have to bite the bullet and send it all to Excel?

 

[Dirk Goldgar]

in
Yhe trick is to use a calculated field in your query
that searches for the min(startdate) which is greater than the
startdate in your current record.

FinishDate: Dmin("startdate", "yourQuery","Startdate > #" &
[startdate] & "# AND [tool designator] = " & [tool designator])

You may need to tweak field names and add some quotemarks if tool
designator is a string.

This could also be done, possibly more efficiently, using a subquery rather
than the VBA function. Something like:

FinishDate:
(SELECT Min(StartDate) FROM YourTable T
WHERE T.StartDate > YourTable.StartDate
AND T.[tool designator] = YourTable.[tool designator])