Help with update query, using result of Max query


J

Jey

I have three tables: one for individual animals, and one each for GPS & VHF
locations for each animal from it's radio collar. Each animal may have from 0
to 100,000s of locates.

I want to update the 'LastSeen' field in Individuals with the date of the
last time it was located. I have a method that is working (with help from
posts from this forum!) for the smaller VHF table. However, when I try the
same method on the MUCH bigger GPS table, it's painfully slow (I haven't had
the patience to let it run longer than 20 minutes, and it's still on the
first tick on the progress bar)! Is there a better way than this:

"qryVHFdateAscending" =

SELECT T1.Individual_ID, T1.Date
FROM VHFLocations AS T1
WHERE (((T1.Date) In (SELECT Max(Date) FROM VHFLocations WHERE Individual_ID
= T1.Individual_ID)))
ORDER BY T1.Individual_ID;

and

"qryLastSeenVHFUpdate" =

UPDATE Individual INNER JOIN qryVHFdateAscending ON Individual.Individual_ID
= qryVHFdateAscending.Individual_ID SET Individual.LastSeen =
qryVHFdateAscending.Date
WHERE (((qryVHFdateAscending.Date)>[Individual].[LastSeen]));

(the last criteria there is to make sure I only update if the new value is
in fact after the existing value)

I tried it using a 'totals' query such as:
SELECT VHFLocations.Individual_ID, Max(VHFLocations.Date) AS MaxOfDate
FROM VHFLocations
GROUP BY VHFLocations.Individual_ID;

which is very fast, but then the second (update) query gives the error
message: "operation must use an updateable query". Is there a way to use the
results of a totals query to feed an update query??

Thanks in advance,
Jey
 
Ad

Advertisements

G

Gary Walter

Jey said:
I have three tables: one for individual animals, and one each for GPS & VHF
locations for each animal from it's radio collar. Each animal may have from 0
to 100,000s of locates.

I want to update the 'LastSeen' field in Individuals with the date of the
last time it was located. I have a method that is working (with help from
posts from this forum!) for the smaller VHF table. However, when I try the
same method on the MUCH bigger GPS table, it's painfully slow (I haven't had
the patience to let it run longer than 20 minutes, and it's still on the
first tick on the progress bar)! Is there a better way than this:

"qryVHFdateAscending" =

SELECT T1.Individual_ID, T1.Date
FROM VHFLocations AS T1
WHERE (((T1.Date) In (SELECT Max(Date) FROM VHFLocations WHERE Individual_ID
= T1.Individual_ID)))
ORDER BY T1.Individual_ID;

and

"qryLastSeenVHFUpdate" =

UPDATE Individual INNER JOIN qryVHFdateAscending ON Individual.Individual_ID
= qryVHFdateAscending.Individual_ID SET Individual.LastSeen =
qryVHFdateAscending.Date
WHERE (((qryVHFdateAscending.Date)>[Individual].[LastSeen]));

(the last criteria there is to make sure I only update if the new value is
in fact after the existing value)

I tried it using a 'totals' query such as:
SELECT VHFLocations.Individual_ID, Max(VHFLocations.Date) AS MaxOfDate
FROM VHFLocations
GROUP BY VHFLocations.Individual_ID;

which is very fast, but then the second (update) query gives the error
message: "operation must use an updateable query". Is there a way to use the
results of a totals query to feed an update query??
Hi Jey,

In similar situations (especially with large dataset) I usually make use
of a temp table (say "tblLastSeen" with 2 fields IndID, LastSeenDate)
that one empties, then feeds with an append query, i.e., change last
query above to append to "tblLastSeen."

Then update "Individual" from "tblLastSeen."

good luck,

gary
 
Ad

Advertisements

J

Jey

Works like a charm. Great, thanks!
Jey


Gary Walter said:
Jey said:
I have three tables: one for individual animals, and one each for GPS & VHF
locations for each animal from it's radio collar. Each animal may have from 0
to 100,000s of locates.

I want to update the 'LastSeen' field in Individuals with the date of the
last time it was located. I have a method that is working (with help from
posts from this forum!) for the smaller VHF table. However, when I try the
same method on the MUCH bigger GPS table, it's painfully slow (I haven't had
the patience to let it run longer than 20 minutes, and it's still on the
first tick on the progress bar)! Is there a better way than this:

"qryVHFdateAscending" =

SELECT T1.Individual_ID, T1.Date
FROM VHFLocations AS T1
WHERE (((T1.Date) In (SELECT Max(Date) FROM VHFLocations WHERE Individual_ID
= T1.Individual_ID)))
ORDER BY T1.Individual_ID;

and

"qryLastSeenVHFUpdate" =

UPDATE Individual INNER JOIN qryVHFdateAscending ON Individual.Individual_ID
= qryVHFdateAscending.Individual_ID SET Individual.LastSeen =
qryVHFdateAscending.Date
WHERE (((qryVHFdateAscending.Date)>[Individual].[LastSeen]));

(the last criteria there is to make sure I only update if the new value is
in fact after the existing value)

I tried it using a 'totals' query such as:
SELECT VHFLocations.Individual_ID, Max(VHFLocations.Date) AS MaxOfDate
FROM VHFLocations
GROUP BY VHFLocations.Individual_ID;

which is very fast, but then the second (update) query gives the error
message: "operation must use an updateable query". Is there a way to use the
results of a totals query to feed an update query??
Hi Jey,

In similar situations (especially with large dataset) I usually make use
of a temp table (say "tblLastSeen" with 2 fields IndID, LastSeenDate)
that one empties, then feeds with an append query, i.e., change last
query above to append to "tblLastSeen."

Then update "Individual" from "tblLastSeen."

good luck,

gary
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top