How can I get output for the last entry in a query 1

[roaml]

Hello,

I have a dilemma that I am unable to resolve.

I have a list of work orders that are assigned to many status types (see sample below). I would like to create a query that pulls the last entry for “WO ID” D114 assigned to John Day. For example, the last entry for John Day is displayed as the very first one “Assigned to Field” and dated 11/29/04.

How or what can I do to get my output.

WO ID Enter Date Enter Time Status Type
D114 11/29/04 1:13:23 PM Assigned to Field
D114 11/23/04 3:22:14 PM Received from PM
D114 9/22/04 7:30:48 AM Completed by PM
D114 9/17/04 6:30:48 AM Assigned to Field
D114 9/17/04 7:30:48 AM Request from Field
D114 9/17/04 7:30:48 AM Received from PM
D114 7/15/04 2:44:16 PM Returned to Construct
D114 7/15/04 2:44:16 PM Received from Field
D114 7/15/04 2:44:16 PM Completed by PM
D114 6/17/04 2:44:16 PM Assigned to Field

Appreciate any help. Thanks.

 

[PHV]

Why using a GROUP BY clause when you don't need aggregate function ?
Anyway what happens if you modify qryGetMaxDateTime like this:
SELECT vwo_number, Max(enter_date+enter_time) AS EnterDateTime FROM wo_status
WHERE enter_date Between [Start Date] And [End Date]
GROUP BY vwo_number;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[roaml]

Modifying the "qryGetMaxDateTime" query with your suggested statement works great.

 

[roaml]

Hello PHV,

Well, thanks to your help I was able to get the MAX function to work. The problem that I am experiencing now is that the query is outputting all of the “Assigned to Field” status types for the criteria dates entered.
For example, I enter the criteria dates as Start Date: 6/1/04 and End Date: 6/30/04. The following output is what I receive. I only need to see the very last “Assigned to Field” status dated 6/17/04.

WO ID Enter Date Enter Time Status Type
D114 6/01/04 1:00:10 PM Assigned to Field
D114 6/17/04 2:44:16 PM Assigned to Field

Any suggestions?

[BEGIN CODE]

SELECT omp.site_id, omp.eh_wo_number, wo_status.enter_date, Max([wo_status].[enter_date]+[wo_status].[enter_time]) AS EnterDateTime, omp.site_id, omp.site_address, wo_status.status_type, wo_status.process_cycle, wo_status.process_dates, wo_status.date_req_from_field, wo_status.doc_type, site_info.proj_tech
FROM site_info RIGHT JOIN (omp INNER JOIN wo_status ON omp.eh_wo_number = wo_status.wo_number) ON (site_info.site_id = omp.site_id) AND (site_info.site_name = omp.site_name) AND (site_info.site_address = omp.site_address)
WHERE (((wo_status.enter_date) Between [Start Date] And [End Date]))
GROUP BY omp.site_id, omp.eh_wo_number, wo_status.enter_date, omp.site_id, omp.site_address, wo_status.status_type, wo_status.process_cycle, wo_status.process_dates, wo_status.date_req_from_field, wo_status.doc_type, site_info.proj_tech, site_info.proj_manager
HAVING (((wo_status.status_type)="Assigned To Field") AND ((site_info.proj_manager)=[Forms]![criteria_proj_manager_dialog]![ProjManager]))
ORDER BY omp.eh_wo_number;

[END CODE]