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]

Something like this ?
SELECT A.*
FROM yourTable A INNER JOIN (
SELECT [WO ID], Max([Enter Date]+[Enter Time]) AS EnterDateTime FROM yourTable GROUP BY [WO ID]
) M ON A.[WO ID]=M.[WO ID] AND (A.[Enter Date]+A.[Enter Time])=M.EnterDateTime
;

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

 

[Golom]

If you just want to see the last entry for a specific "WO ID" (rather than the last one for more than one "WO ID") then

Select TOP 1 [WO ID], [Enter Date], [Enter Time], [Status Type]

From tbl

Where [WO ID] = 'D114'

Order By [Enter Date] DESC, [Enter Time] DESC

It's a bit nastier of you want to see the last one for more than one [WO ID].

 

[roaml]

Thanks for the response PHV and Golom.

I tried the sql statement that Golom provided and I am receiving the very record for John Day. How can I get all of the records for John Day and just display the last record per workorder?

This is what I am currently receiving.

D114 11/29/04 1:13:23 PM Assigned to Field
D114 11/23/04 3:22:14 PM Received from PM
D115 9/22/04 7:30:48 AM Completed by PM
D115 11/23/04 3:22:14 PM Received from PM
D115 9/22/04 7:30:48 AM Completed by PM

This is what I would like to output.

D114 11/29/04 1:13:23 PM Assigned to Field
D115 9/22/04 7:30:48 AM Completed by PM

I appreciate your help. Thanks.

 

[PHV]

And what about my suggestion ?

 

[roaml]

PHV,

I my real savy with sql and could not get your statment to work.

 

[PHV]

could not get your statment to work
Any error message ? Unexpected result ?

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

 

[JonFer]

roaml - Access 97 doesn't support PHV's SQL. Is that the problem?


John

http://www.interautomateconsulting.com

 

[roaml]

JonFer,

I am using Access 2000.

 

[roaml]

Hi all,

This is the error message I am receiving.

“Missing), ], or Item in query
expression ‘wo_issued_and_open_qry.proj_tech
(Select [vmo_number], Max ([Enter_Date]+[Enter_Time])
AS EnterDateTime
From wo_issued_and_open_qry
WHERE(((wo_issued_and_open_qry.proj_manager)=[Forms]!
[criteria_proj_manager_dialog]![ProjManager]) AND
((wo_iss’. “

Below is the sql statement that I am attempting to run.

SELECT wo_issued_and_open_qry.id, wo_issued_and_open_qry.proj_manager, wo_issued_and_open_qry.vwo_number, wo_issued_and_open_qry.enter_date, wo_issued_and_open_qry.enter_time, wo_issued_and_open_qry.fac_id, wo_issued_and_open_qry.site_address, wo_issued_and_open_qry.status_type, wo_issued_and_open_qry.process_cycle, wo_issued_and_open_qry.process_dates, wo_issued_and_open_qry.date_req_from_field, wo_issued_and_open_qry.doc_type, wo_issued_and_open_qry.proj_tech
(SELECT [vwo_number], Max ([Enter_Date]+[Enter_Time]) AS EnterDateTime
FROM wo_issued_and_open_qry
WHERE(((wo_issued_and_open_qry.proj_manager)=[Forms]![criteria_proj_manager_dialog]![ProjManager]) AND ((wo_issued_and_open_qry.enter_date) Between [Start Date] And [End Date]))
ORDER BY wo_issued_and_open_qry.vwo_number, wo_issued_and_open_qry.enter_date, wo_issued_and_open_qry.enter_time;

HELP!!!!

 

[PHV]

Simply a missing coma:
wo_issued_and_open_qry.proj_tech[highlight],[/highlight]

Anyway you haven't explain why my suggestion doesn't work for you ...

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

 

[roaml]

PHV,

I am trying to execute your statement but received the error message I provided. Am I not following your sample suggestion?

 

[PHV]

Am I not following your sample suggestion?
I don't think so...

SELECT A.id, A.proj_manager, A.vwo_number, A.enter_date, A.enter_time, A.fac_id, A.site_address, A.status_type, A.process_cycle, A.process_dates, A.date_req_from_field, A.doc_type, A.proj_tech
FROM wo_issued_and_open_qry A INNER JOIN (
SELECT vwo_number, Max(enter_date+enter_time) AS EnterDateTime FROM wo_issued_and_open_qry
WHERE enter_date Between [Start Date] And [End Date]
GROUP BY vwo_number
) M ON A.vwo_number=M.vwo_number AND (A.enter_date+A.enter_time)=M.EnterDateTime
WHERE A.proj_manager=[Forms]![criteria_proj_manager_dialog]![ProjManager]
ORDER BY A.vwo_number, A.enter_date, A.enter_time;

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

 

[roaml]

Hello PHV,

I took a break from this last issue and now I’m determined to get it to run.

I am still having problems trying to execute the code. I received the error message below:

[Begin Error Message]
WO_Status_DB can’t represent the join expression
A.vwo_number = M.vwo_number AND
(A.enter_date+A.enter_time) = M.EnterDateTime in Design
view.
[End Error Message]

[Begin Code]
SELECT A.id, A.proj_manager, A.vwo_number, A.enter_date,
A.enter_time, A.status_type
FROM wo_issued_and_open_qry A INNER JOIN (
SELECT vwo_number, Max(enter_date+enter_time)AS
EnterDateTime
FROM wo_issued_and_open_qry
WHERE enter_date Between [Start Date] And [End Date]
GROUP BY vwo_number
)M ON A.vwo_number = M.vwo_number AND
(A.enter_date+A.enter_time) = M.EnterDateTime
WHERE A.proj_manager=[Forms]!
[criteria_proj_manager_dialog]![ProjManager]
ORDER BY A.vwo_number, A.enter_date, A.enter_time;
[End Code]

As usual, your help is appreciated. Thanks!

 

[JonFer]

Can you run the query? That message is only about displaying the query design window, not the results.

If you want to see the query design, move the criteria "(A.enter_date+A.enter_time) = M.EnterDateTime" from the ON clause to the WHERE clause.

John

http://www.interautomateconsulting.com

 

[roaml]

Hello All,

I am receiving the error message "Syntax error in FROM clause". What am I missing?

[BEGIN CODE]

SELECT A.id, A.proj_manager, A.vwo_number, A.enter_date, A.enter_time, A.fac_id, A.site_address, A.status_type, A.process_cycle, A.process_dates, A.date_req_from_field, A.doc_type, A.proj_tech
FROM wo_issued_and_open_qry A INNER JOIN (
SELECT vwo_number, Max(enter_date+enter_time) AS EnterDateTime FROM wo_issued_and_open_qry
WHERE enter_date Between [Start Date] And [End Date]
GROUP BY vwo_number
) M ON A.vwo_number=M.vwo_number AND (A.enter_date+A.enter_time)=M.EnterDateTime
WHERE A.proj_manager=[Forms]![criteria_proj_manager_dialog]![ProjManager]
ORDER BY A.vwo_number, A.enter_date, A.enter_time;

[END CODE]

HELP!!!

 

[PHV]

You may try this alternate method.
Create a saved query named, say, qryGetMaxDateTime:
SELECT vwo_number, Max(enter_date+enter_time) AS EnterDateTime FROM wo_issued_and_open_qry
WHERE enter_date Between [Start Date] And [End Date]
GROUP BY vwo_number;

Then your query becomes:
SELECT A.id, A.proj_manager, A.vwo_number, A.enter_date, A.enter_time, A.fac_id, A.site_address, A.status_type, A.process_cycle, A.process_dates, A.date_req_from_field, A.doc_type, A.proj_tech
FROM wo_issued_and_open_qry A INNER JOIN qryGetMaxDateTime M
ON A.vwo_number=M.vwo_number AND (A.enter_date+A.enter_time)=M.EnterDateTime
WHERE A.proj_manager=[Forms]![criteria_proj_manager_dialog]![ProjManager]
ORDER BY A.vwo_number, A.enter_date, A.enter_time;

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

 

[roaml]

PHV,

The first query ran great but the second one presented this error:

Circular reference caused by ‘qryGetMaxDateTime’.

 

[PHV]

What is the SQL code of wo_issued_and_open_qry ?

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

 

[roaml]

Here is the code for "wo_issued_and_open_qry":

SELECT wo_status.ID, wo_site_info.proj_manager, wo_status.vwo_number, wo_status.enter_date, wo_status.enter_time, wosite.cell_id, wosite.site_address, wo_status.status_type, wo_status.process_cycle, wo_status.process_dates, wo_status.date_req_from_field, wo_status.doc_type, wo_site_info.proj_tech

FROM wo_site_info INNER JOIN (wosite INNER JOIN wo_status ON wosite.ehs_vwo_number = wo_status.vwo_number) ON (wo_site_info.site_id = wosite.site_id) AND (wo_site_info.site_name = wosite.site_name) AND (wo_site_info.site_address = wosite.site_address)

GROUP BY wo_status.ID, wo_site_info.proj_manager, wo_status.vwo_number, wo_status.enter_date, wo_status.enter_time, wosite.site_id, wosite.site_address, wo_status.status_type, wo_status.process_cycle, wo_status.process_dates, wo_status.date_req_from_field, wo_status.doc_type, wo_site_info.proj_tech, wo_status.ID

ORDER BY wo_status.vwo_number, wo_status.enter_date, wo_status.enter_time;