First and Last Functions in Crosstab Query (1 Viewer)

fishboy

New member
Local time
Today, 02:00
Joined
Jan 28, 2010
Messages
4
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....
 

fishboy

New member
Local time
Today, 02:00
Joined
Jan 28, 2010
Messages
4
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
 

olxx

Registered User.
Local time
Yesterday, 19:00
Joined
Oct 2, 2009
Messages
52
"...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.
 

fishboy

New member
Local time
Today, 02:00
Joined
Jan 28, 2010
Messages
4
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.
 

fishboy

New member
Local time
Today, 02:00
Joined
Jan 28, 2010
Messages
4
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.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:00
Joined
Aug 30, 2003
Messages
36,118
Excellent! Glad it helped you out.
 

Users who are viewing this thread

Top Bottom