Trying to Query Lates Date (1 Viewer)

net

Registered User.
Local time
Today, 15:01
Joined
Mar 12, 2006
Messages
50
Hello,

I have an Access Query to pull work orders to identify projects that have existing work orders processed.

On one project, I may have multiple work orders. I would only like to pull the latest (last) work order processed. I have a WO_Processed_Date field that I would like to use to pull the latest date for the work order processed.

Example:

WO Num WO_Processed_Date
WO24444 3/1/2018
WO24444 3/5/2018
WO24444 4/6/2018
WO24444 5/20/2018
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:01
Joined
Feb 19, 2013
Messages
16,553
databases have no concept of 'last' without the application of an order to the data. based on the info provided you do not have sufficient information to determine the last - you would need a timestamp field for example. You might get away with an autonumber field but it is not reliable for this use in a multi user environment.

Edit: just realised I'm thinking UK date structure (just one of those nights)

You need to use a domain function, sub query or virtual table to determine the maximum date

so something like

SELECT Max(processdate) as MaxDate FROM myTable

then apply to your query

SELECT myTable.* FROM myTable INNER JOIN maxQry ON myTable.processdate=maxqry.maxdate

or you can use TOP 1 and order by

SELECT TOP 1 * FROM myTable ORDER BY processdate DESC

or a subquery

SELECT * FROM myTable WHERE processdate=(SELECT Max(processdate) as MaxDate FROM myTable T)

note the T alias in the subquery, it is required
 
Last edited:

net

Registered User.
Local time
Today, 15:01
Joined
Mar 12, 2006
Messages
50
Hello CJ,

Thank you for responding. I have uploaded a sample db of my data. I have an autonumber, but no auto date on the table. I have a timestamp setup in my data entry form, but not the table.
 

Attachments

  • WO_TEST_DB.accdb
    880 KB · Views: 249

Mark_

Longboard on the internet
Local time
Today, 15:01
Joined
Sep 12, 2017
Messages
2,111
Net,

Have you tried

Code:
SELECT TOP 1 * FROM myTable ORDER BY processdate DESC

This will give you the last in date order. It is also useful as you can change the "1" to any given number if you have need of more than one (say the last 5).
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:01
Joined
Feb 19, 2002
Messages
42,976
You might get away with an autonumber field but it is not reliable for this use in a multi user environment.
Why not? autonumbers are assigned sequentially. So 8393 precedes 8394. The issue is if items might be entered out of order. In that case, the autonumber alone won't work. I use both a date and an autonumber and sort by date first and autonumber second. If that method won't work, then you need a custom sequence number that you can generate and leave gaps so inserts can happen between 110 and 120.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:01
Joined
May 7, 2009
Messages
19,169
On 1 wo you have diff proj, site, wo rcv date?
 

net

Registered User.
Local time
Today, 15:01
Joined
Mar 12, 2006
Messages
50
Thank you everyone with your suggestions. :)

As arnelgp pointed out, I provided incorrect sample data. I have uploaded an updated db.

Using the SQL statements provided by CJ and Mark, the query returns 1 work order (WO24420) with WO_Processed_Date as 5/13/2018.

I would like to see all work orders with the most current date in my output. I have a total of over 600 records and all I need to see is all the Work Order Numbers with the most current WO_Processed_Date for my analysis.

I really appreciate all of you helping me with my query issue. I am not SQL savvy, so this is somewhat frustrating. :eek:
 

Attachments

  • WO_TEST_DB.accdb
    880 KB · Views: 238

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:01
Joined
May 7, 2009
Messages
19,169
SELECT tbl_WO_Data.[WO Number], tbl_WO_Data.Project_Number, tbl_WO_Data.Site_ID, Min(tbl_WO_Data.Date_WO_Received) AS MinOfDate_WO_Received, Max(tbl_WO_Data.WO_Processed_Date) AS MaxOfWO_Processed_Date, tbl_WO_Data.[Site Vendor], tbl_WO_Data.Equipment_Manager, (SELECT TOP 1 T1.[WO STATUS] FROM TBL_WO_DATA AS T1 WHERE (T1.[WO NUMBER] & T1.PROJECT_NUMBER & T1.SITE_ID)=(TBL_WO_DATA.[WO NUMBER] & TBL_WO_DATA.PROJECT_NUMBER & TBL_WO_DATA.SITE_ID) ORDER BY T1.WO_PROCESSED_DATE DESC) AS [WO STATUS]
FROM tbl_WO_Data
GROUP BY tbl_WO_Data.[WO Number], tbl_WO_Data.Project_Number, tbl_WO_Data.Site_ID, tbl_WO_Data.[Site Vendor], tbl_WO_Data.Equipment_Manager;
 

Mark_

Longboard on the internet
Local time
Today, 15:01
Joined
Sep 12, 2017
Messages
2,111
For a more modular approach,

use DMax( "YourDateField","YourTable","<Criteria(Optional)=Value>") to find out what date you need then feed this to your query as the date you are looking for.

The single advantage this has is you could use the date returned by DMax() to fill a control on screen so you don't have to show the date for each of the work orders.

There are multiple ways to achieve what you want, which one will work best for you is dependent on what else you want to do for presentation.
 

Users who are viewing this thread

Top Bottom