Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2018, 02:22 PM   #1
net
Newly Registered User
 
Join Date: Mar 2006
Posts: 43
Thanks: 4
Thanked 0 Times in 0 Posts
net is on a distinguished road
Trying to Query Lates Date

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

net is offline   Reply With Quote
Old 05-17-2018, 02:31 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,949
Thanks: 38
Thanked 3,223 Times in 3,123 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Trying to Query Lates Date

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 05-17-2018 at 02:38 PM.
CJ_London is offline   Reply With Quote
Old 05-17-2018, 03:27 PM   #3
net
Newly Registered User
 
Join Date: Mar 2006
Posts: 43
Thanks: 4
Thanked 0 Times in 0 Posts
net is on a distinguished road
Re: Trying to Query Lates Date

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.
Attached Files
File Type: accdb WO_TEST_DB.accdb (880.0 KB, 4 views)

net is offline   Reply With Quote
Old 05-17-2018, 03:35 PM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,089
Thanks: 13
Thanked 203 Times in 201 Posts
Mark_ will become famous soon enough
Re: Trying to Query Lates Date

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).
Mark_ is offline   Reply With Quote
Old 05-17-2018, 05:20 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,925
Thanks: 13
Thanked 1,315 Times in 1,254 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Trying to Query Lates Date

Quote:
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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-17-2018, 09:38 PM   #6
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,963
Thanks: 54
Thanked 1,920 Times in 1,834 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Trying to Query Lates Date

On 1 wo you have diff proj, site, wo rcv date?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 05-17-2018, 10:38 PM   #7
net
Newly Registered User
 
Join Date: Mar 2006
Posts: 43
Thanks: 4
Thanked 0 Times in 0 Posts
net is on a distinguished road
Re: Trying to Query Lates Date

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.
Attached Files
File Type: accdb WO_TEST_DB.accdb (880.0 KB, 4 views)

net is offline   Reply With Quote
Old 05-17-2018, 11:26 PM   #8
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,963
Thanks: 54
Thanked 1,920 Times in 1,834 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Trying to Query Lates Date

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;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 05-18-2018, 07:47 AM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,089
Thanks: 13
Thanked 203 Times in 201 Posts
Mark_ will become famous soon enough
Re: Trying to Query Lates Date

For a more modular approach,

use DMax( "YourDateField","YourTable","<Criteria(Optional)=V alue>") 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.

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to format date values in query criteria to search for short date MilaK Queries 16 04-19-2017 12:01 AM
Query Table1 date to Table2 column1 begin date range to column2 end date range shardez Queries 10 03-10-2014 12:14 PM
Query criteria Date() not accurate for fields set as General Date (date/time) isaacski Queries 3 05-24-2013 06:51 PM
need help making a query(s) to categorize time between ship date and repair date weeblebiker Queries 1 04-04-2013 10:05 AM
Calculating a review date based on a date field and a non date field in a query Sweetnuff38 Queries 4 10-15-2010 03:49 PM




All times are GMT -8. The time now is 04:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World