First and Last Functions in Crosstab Query

I’m using Access 2003.
I have some fish tracking data that I’m trying to interrogate using crosstab queries. The table ALL DATA contains the fields; ‘Tag ID’, ‘Date’, ‘Time’, ‘Date and Time’, ‘km’ and ‘Rec Name’.

I want to find out where each fish is at the start of each week in km, and where it is at the end of each week in km. Using the Crosstab query below for the start of the week (therefore the first record)…

TRANSFORM First([ALL DATA].km) AS FirstOfkm
SELECT [ALL DATA].[Tag ID]
FROM [ALL DATA]
GROUP BY [ALL DATA].[Tag ID]
PIVOT Format([Date],"ww yyyy");

However the query does not return the correct values, The Help says about the First and Last functions…. “Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.”

How do I include an ORDER BY clause as suggested?? I want to order by the field ‘Date and Time’.

Many Thanks....

Will Min and Max work for you?

http://support.microsoft.com/kb/208190

Thanks for that link Paul. So the first and last functions don't work.... Any other way around it? Min and Max aren't appropriate.

cheers chris

"...I want to find out where each fish is at the start of each week in km.." I don´t get it. Can you be more specific, please.

The table holds over a million records, each record is fish specific 'Tag ID' (43 different fish) and holds the 'date and time' and the position of the fish in 'km', km's from the downstream limit of the study area, that each fish was detected. There are multiple records for each fish each day. I want to work out the the distance between the first record for each fish and the last record for each fish every week. I was hoping to use the above crosstab query to return the 'km' value for the first detection each week, and minus that from the 'km' value for the last detection each week; to give the result.

Thanks.

I suspect you'd need to find the Min and Max of the date/time field, and then get the km associated with it. This might help:

http://www.baldyweb.com/LastValue.htm

Genius !!

I crosstab’ed the min date and time for each week for each fish, then added in new columns and used update queries to populate these with the km value for that date and time for each fish. Took one week from the next to give the result.

Many thanks baldy.

Excellent! Glad it helped you out.