Queries try to the most current

  • Thread starter Thread starter Michaelc
  • Start date Start date
M

Michaelc

i'm trying to find the most recent date from this field.
dbo_TRACKED_OBJECT_HISTORY.complete_time

Here's the queries or SQL can somebody help me.

SELECT dbo_UNIT.serial_number, dbo_UNIT.part_number, dbo_UNIT.part_revision,
dbo_TRACKED_OBJECT_STATUS.op_name, dbo_TRACKED_OBJECT_HISTORY.op_name,
dbo_TRACKED_OBJECT_HISTORY.complete_time
FROM (dbo_UNIT INNER JOIN dbo_TRACKED_OBJECT_STATUS ON dbo_UNIT.unit_key =
dbo_TRACKED_OBJECT_STATUS.tobj_key) INNER JOIN dbo_TRACKED_OBJECT_HISTORY ON
dbo_TRACKED_OBJECT_STATUS.tobj_key = dbo_TRACKED_OBJECT_HISTORY.tobj_key
WHERE (((dbo_TRACKED_OBJECT_STATUS.op_name)="SA_TRuepoint_XCVR_Debug"));
 
Hi Michael,

I have reformetted your SQL to be easier to read:

SELECT dbo_UNIT.serial_number
, dbo_UNIT.part_number
, dbo_UNIT.part_revision
, dbo_TRACKED_OBJECT_STATUS.op_name
, dbo_TRACKED_OBJECT_HISTORY.op_name
, dbo_TRACKED_OBJECT_HISTORY.complete_time
FROM (dbo_UNIT
INNER JOIN dbo_TRACKED_OBJECT_STATUS
ON dbo_UNIT.unit_key = dbo_TRACKED_OBJECT_STATUS.tobj_key)
INNER JOIN dbo_TRACKED_OBJECT_HISTORY
ON dbo_TRACKED_OBJECT_STATUS.tobj_key =
dbo_TRACKED_OBJECT_HISTORY.tobj_key
WHERE (((dbo_TRACKED_OBJECT_STATUS.op_name)="SA_TRuepoint_XCVR_Debug"));

~~~

add this to your criteria:

dbo_TRACKED_OBJECT_HISTORY.complete_time =
dMax("complete_time", "dbo_TRACKED_OBJECT_HISTORY")

now, this does not consider dbo_TRACKED_OBJECT_STATUS.op_name ... if you
do need to consider that, you could do this:

make a query with
dbo_TRACKED_OBJECT_STATUS.op_name
dbo_TRACKED_OBJECT_HISTORY.complete_time

make it a totals query and get the max of
dbo_TRACKED_OBJECT_HISTORY.complete_time

then, the criteria for your initial query would be:

dLookup("complete_time", "queryname", "op_name='" &
dbo_TRACKED_OBJECT_STATUS.op_name & "'")

.... assuming op_name is a string


Warm Regards,
Crystal

*
(: have an awesome day :)
*
 
Back
Top