Select latest recorded record in Access crosstab query

[mgidron]

I have a table that logs timestamped records.
How do you select the latest timestamp records for all items?

Example: With Item id 3, listed below I want only the latest recorded
record.


ITEM ID TEMP TIME HOUROFTEMP
------- ---- ---- ----------
1 100 9:20 PM 9:00 PM
3 90 9:10 PM 9:00 PM *
5 78 9:05 PM 9:00 PM
3 89 9:45 PM 9:00 PM * Need to select this record *
2 99 9:23 PM 9:00 PM

qry = selects all records for today

TRANSFORM First(qry.TEMP) AS FirstOfTemp
SELECT qry.ITEMNAME, qry.TIME
FROM qry
GROUP BY qry.ITEMNAME, qryTMod_Stage.TIME
PIVOT Format(qry.HOUROFTEMP,"h:nnAM/PM") In
("11:00AM","12:00PM","1:00PM","2:00PM","3:00PM","4:00PM",
"5:00PM","6:00PM","7:00PM","8:00PM","9:00PM","10:00PM");

Thanks,
Michael Gidron
(e-mail address removed)
(e-mail address removed)

 

[MGFoster]

I have a table that logs timestamped records.
How do you select the latest timestamp records for all items?

Example: With Item id 3, listed below I want only the latest recorded
record.


ITEM ID TEMP TIME HOUROFTEMP
------- ---- ---- ----------
1 100 9:20 PM 9:00 PM
3 90 9:10 PM 9:00 PM *
5 78 9:05 PM 9:00 PM
3 89 9:45 PM 9:00 PM * Need to select this record *
2 99 9:23 PM 9:00 PM

qry = selects all records for today

TRANSFORM First(qry.TEMP) AS FirstOfTemp
SELECT qry.ITEMNAME, qry.TIME
FROM qry
GROUP BY qry.ITEMNAME, qryTMod_Stage.TIME
PIVOT Format(qry.HOUROFTEMP,"h:nnAM/PM") In
("11:00AM","12:00PM","1:00PM","2:00PM","3:00PM","4:00PM",
"5:00PM","6:00PM","7:00PM","8:00PM","9:00PM","10:00PM");

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why isn't the "qryTMod_Stage" in the FROM clause? Do you just have one
data source, "qry"? If so, like this (I'll change the data source's
name to "table_name." You can change it to whatever you are using):

TRANSFORM First(TEMP) AS FirstOfTemp
SELECT ITEMNAME, TIME
FROM table_name As T
WHERE Time = (SELECT MAX(Time) FROM table_name
WHERE ItemID = T.ItemID)
GROUP BY ITEMNAME, TIME
PIVOT Format(HOUROFTEMP,"h:nnAM/PM") In
("11:00AM","12:00PM","1:00PM","2:00PM","3:00PM","4:00PM",
"5:00PM","6:00PM","7:00PM","8:00PM","9:00PM","10:00PM");

If you have a date-column you may want to separate the results by day,
like this: Change the sub-query's WHERE clause to this:

... WHERE ItemID = T.ItemID AND date_column = T.date_column)

use your date column's name in place of date_column.

BTW, you should use other column names in place of "TEMP" and "TIME"
because TIME is a VBA function name and TEMP is the common name of the
temporary folder and for temporary variable names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzTT34echKqOuFEgEQLMkwCgw0rDsw3xVArpTApcw4VOALaia60AoP6C
m7qm9PvJvDfc8TWlC4e2sdz9
=zUBB
-----END PGP SIGNATURE-----

 

[mgidron]

Thanks MGFoster! Worked perfect... I never thought about the where clause.