Hello, I am having trouble creating a query that will select the first record with the greatest date/time from a single table. The database is an equipment database I am designing for work and the table I am querying keeps track of every status change of a specific piece of equipment.

The table is designed as such:

Equipment-ID Number A unique ID Number for each piece of equipment
Status-ID Number The Status in which a piece of equipment was changed to
Date Short Date The Date in which the equipment status was changed
Time Short Time THe time in which the equipment staus was changed

The PK of the table includes all 4 attributes

Sample data would be as such:

00 1 12/31/2012 00:00
00 2 12/31/2012 00:01
00 2 12/30/2012 00:03
01 1 12/15/2012 00:00
02 1 12/15/2012 00:00
03 1 12/15/2012 00:00

The result of the query should return the Equipment and Status ID with the most resent status change for each equipment item. IE:
00 2 12/31/2012 00:01
01 1 12/15/2012 00:00
02 1 12/15/2012 00:00
03 1 12/15/2012 00:00


The results should omit the first and third records as the second record would be the most recent. I am having trouble getting my query to work for this. I have tried Select Distinct, Order by decending Date, Time then selecting the first Equipment-ID, etc. Any direction on this would be great. I am still a beginner when it comes to SQL but I can fumble with it. Thank you in advance.

Hi,

(IMG:style_emoticons/default/welcome2UA.gif)

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

The simplest is to use two separate queries to get there. For example:

qryLatestStatusDates
SELECT EquipmentID, Max([DateField]+[TimeField]) As LastStatusDate
FROM TableName
GROUP BY EquipmentID
ORDER BY EquipmentID

Then,

qryLatestStatusID
SELECT EquipmentID, StatusID, [DateField], [TimeField]
FROM TableName
INNER JOIN qryLatestStatusDates
ON TableName.EquipmentID=qryLatestStatusDates.EquipmentID
AND [DateField]+[TimeField]=qryLatestStatusDates.LastStatusDate
ORDER BY EquipmentID

(untested)
Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi

Welcome to UtterAccess (IMG:style_emoticons/default/welcome2UA.gif)

My suggestion would be to change your table design if possible. Changes I'd suggest would be to do away with your current compound primary key and add an Autonumber which you'd use as the PK, then get rid of the separate time field and just use one table field to store both the date and time as a single piece of data. If you need the date and/or time individually you can always get them easily using DateValue() and TimeValue().

With the new table structure a simple Totals query should give you the results your looking for

Thanks guys I will try that. I am using 2010 at work and 2007 at home (i have to comment out some VBA that I use for form styling when switching to 2007 but thats it). This is my first Access project outside of the classroom and reports is where I am week. Trying to build up my strength in that area and these last 2 reports are killing me on this project.

Hi

(IMG:style_emoticons/default/yw.gif) - DBG and I are happy to help. Continued success with your project