Extracting record(s) with most recent date

[BrianM2]

I have a database table comprising inspection records for about 300 process control instruments. A new record is entered for each inspection so that an inspection history builds up for each instrument.

For one report I need to extract all the records with the only the latest inspection date for each instrument.

I attempted to build a query using the design grid screen. Initially I thought I could use the "Max of" approach as I have done with numbers. This did not work so tried without success to convert the dates to serial format thinking I could "Max of".

Have now concluded that a module is needed. However with my Access Basic programming skills this would take me a couple of weeks and still be wrong!

Can anyone point me in the right direction?

 

[mhartman]

Hello
Build your query with your date field desired. Then for the most recent date, use "Sort Ascending" for the sort order. This will put the most recent date first.
Regards
Mark

 

[raskew]

Hi -

First check to ensure your InspectionDate field is in date/time data format. To return the most recent InspectionDate for each instrument, try copying/pasting this to a new query, replacing table/field names as necessary:

SELECT DISTINCT
    t.CustomerID
  , Orders3.OrderID
  , Orders3.OrderDate
FROM
   Orders3 AS t 
LEFT JOIN
   Orders3 
ON
   t.CustomerID = Orders3.CustomerID
WHERE
   (((Orders3.OrderDate) In (
SELECT
    Top 1 [OrderDate] 
FROM
   Orders3 
WHERE
   [CustomerID]= t.[CustomerID] 
ORDER BY
   [OrderDate] Desc)));

Orders3 is a copy of Northwind's Orders table used for testing purposes.
CustomerID would correlate to your Instrument ID; OrderDate --> Inspection Date; and OrderID --> InspectionID (if in fact you have such a thing, if not delete it).

Tested in A97

HTH - Bob

 

[Brianwarnock]

I do not understand why Max did not work, I, and others on the forum,have used it successfully on dates so that cannot be the issue.

Because it creates an aggregate query frequently more than 1 query is needed to extract all the data, typically a groupby on a unique ID plus max on the date forms query1 and this plus the table joined on ID is forms query2 to extract the rest of the data.

Brian

This is wrong in detail see later post

 

[BrianM2]

Hi all - ie Mark, Bob and Brian.

I appreciate you all taking the time to help with the problem. There has been some delay in getting back to you and for that I apologise. It is partly because I am on the other side of the world but also I only got back to the office at 5.30 pm today. It's now 8.00 pm on Monday evening.

I see that you are all advocating sql as the way to go.

Mark

I agree that I can order by date and get the individual records to sort. My problem has however to been to get only the most recent date into a query to use in the reports.

Bob

I see that you are suggesting the use of "top" to extract the newest record. That looks as if it is the way to go. I'll certainly try it. Thank you.

Brian

I'm interested that you have found 'max' works with dates. I tried making up a very simple set of records and it still would not work. I'm thinking now that I should have converted the dates to "US" format rather than the dd/mmm/yyyy format that we use. Will try that also.

Btw - as a retired Liverpool man you may remember the old Metrovic/AEI factory at Trafford Park, Manchester. I did a graduate apprenticeship (I'm an electrical engineer) there more years ago than I care to remember. very fond memories!


Thanks for your help. Will get back to you over the next week or two to let you know how I've got on. Will be working away from the office for a while.

Brian

 

[Brianwarnock]

Hi Brian,
The format is irrelevant as it is dealing with the stored format which is a form off number, I forget the details, I use the good old English format for display so no problem there. Do you want to zip and attach a DB for us to look at?

Brian

PS I try to avoid Manchester they have a real crappy football team there

 

[Brianwarnock]

Hi just realised I made a mistake in the detail of an earlier post.

In your case you would need to group by instrument and Max date in query1,
then query2 would take query1 and the table, join on MaxofDate and original date field, and on instrument, to get rest of data.

Brian

 

[BrianM2]

Brian,

Thanks again. Will look at this a soon as I can.

If I can not get it to work will send a modified form of the database over to you. I say modified because it uses two databases for information. One being 9MB & the other 6. I'd need to cut it down and sort out the paths.

Will be in touch.

Brian

PS - Have heard of that team. Over here however football means an oval ball!

 

[BrianM2]

Brian

I'm afraid I never could get Max t work for me on this one. I ended up by using Bob Askew's code incorporating an IN statement.

However will keep trying when I have a little time and will let you know if I ever make it!

Many thanks for your assistance.


Brian

 

[raskew]

Hi Brian -

Try this, substituting table/names as appropriate:

SELECT Orders3.CustomerID, Max(Orders3.OrderDate) AS MaxOfOrderDate
FROM Orders3
GROUP BY Orders3.CustomerID;

Bob

 

[Brianwarnock]

Hi Bob this is what I was suggesting in my probably less than clear post.
This query is then joined to the tables to get the rest of the data, it should work.

BrianWarnock (full name to avoid confusion with 2 Brians posting )

 

[BrianM2]

BrianW/Bob

Thanks for the additional code. This call for help was generated by work which I do for a chemical company client for which I am electrical and instrumentation engineer. I'll shortly be getting on an aircraft to spend a week with them - which I do every couple of weeks. So will continue the discussion from my desk in the North Island.

Will be in touch

BrianM

 

[natep26]

I'd be interested in finding out how you set up your inspection history side of the database if you have time.

Thanks