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....
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....