A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with update query, using result of Max query



 
 
Thread Tools Display Modes
  #1  
Old April 10th, 2008, 10:40 PM posted to microsoft.public.access.queries
Jey
external usenet poster
 
Posts: 27
Default 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  
Old April 11th, 2008, 01:42 PM posted to microsoft.public.access.queries
Gary Walter
external usenet poster
 
Posts: 613
Default 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  
Old April 11th, 2008, 05:37 PM posted to microsoft.public.access.queries
Jey
external usenet poster
 
Posts: 27
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.