If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with update query, using result of Max query
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 |
#2
|
|||
|
|||
Help with update query, using result of Max query
"Jey" wrote: 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 |
#3
|
|||
|
|||
Help with update query, using result of Max query
Works like a charm. Great, thanks!
Jey "Gary Walter" wrote: "Jey" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|