Time Based Sort/Retrieval

[l0knezmonstir]

Need help on a query here. I have a table that consists of an ID, a color(text), a date, and a time. The ID shows up mulitple times. I am using Access 2000.
Sample: 17324 yellow 3/13/2003 4:55:03 PM
I need the query to return a list of the ID numbers and the color of the latest change. There should be a way to do this, but I think its a bit over my head as I am somewhat new SQL.

-Marty

 

[rac2]

Create a query that finds the most recent color for every ID. Let's call that query LatestChanges. It will have two columns, let's call them ID and last_changed. Join this query with the table of changing colors, let's call that table ColorChanges; it already has a name but you didn't mention it.

I will assume that the date and time of the change are stored in one column named date_changed.

The SQL defining LatestChanges is:

SELECT ID,
       MAX(date_changed) AS "last_changed"
FROM ColorChanges
GROUP BY ID

The SQL joining the new query and the existing table is:

SELECT ID,
       color
FROM ColorChanges
JOIN LatestChanges ON ColorChanges.ID = LatestChanges.ID
                  AND ColorChanges.date_changed = LatestChanges.last_changed

This is a technique that you can apply in any situation where you need to show both an aggregate result and many of the descriptive attributes of an entity. Create a view to obtain the aggregate for all of the entities, summarize by the ID values; then join that view to a table with all of the interesting details about each entity.

The particular example here is a variation in that we do not have a table of the entities referred to by the ID. If we did then the join condition would be a little different. Suppose these IDs are referring to Chameleons and we have a table of information about the Chameleons in our garden, ID, name, date_of_hatch, tail_missing, that kind of thing.

We could use the aggregate query defined above to report on the current color of each Chameleon. In fact let's keep track of where the fellas were sitting when the color changed in the table of ColorChanges in a columnn named sitting_on.

SELECT Chameleons.name,
       ColorChanges.color,
       ColorChanges.sitting_on,
       LatestChanges.last_changed
FROM Chameleons
JOIN ColorChanges ON Chameleons.ID = ColorChanges.ID
JOIN LatestChanges ON Chameleons.ID = LatestChanges.ID
                  AND ColorChanges.date_changed = 
LatestChanges.last_changed

Well, time to go back to work.

 

[l0knezmonstir]

I follow it all, one problem though. The date and time are in two different fields. How would i get around that? Thanks for the help.
-Marty

 

[rac2]

Yes, I suspected that. That is an odd thing because it may imply that the two columns are not DATETIME data types. And that would mean it would be very difficult to order them by sorting or using a MAX function.

If they are actually DATETIME columns then MAX can give you the most recent date, and the most recent time. But that won't be much help with colors that are changing repeatedly during the same day.

One solution would be to convert the date and time values to strings, if they are not already strings. Then concatenate them into a single string and convert that string to a DATETIME value. So instead of MAX(date_changed) you will use

MAX(   CDate(  CStr(change_date) & " " & CStr(change_time)  )   )

One more thought. I would use one column to record when the color changed. Give it the default value Now() and let the system fill in the time whenever a row is added. This will be displayed as a date and time; and the way it is displayed can be controlled with formatting functions.
You could add such a column and update it with an expression like the one above. Then delete the present columns for date and time.