RE: how do I update multiple queries at once?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Gentlemen: Thanks for getting back to me so promptly. Gerry: Here is the
SQL statement (this particular fish's 'ID number' is 1523):

SELECT Receivers.transmitter, Receivers.location, Receivers.date,
Receivers.time
FROM Receivers
WHERE (((Receivers.transmitter)=1523));

Thanks again, Paul

"Jerry Whittle" wrote:

Hi Paul,

Open up one of your existing queries in design view. Go to View on the menu
and select SQL View. Copy the words that you see, (A.K.A. the SQL statement)
and paste them here. I'll see what I can do.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Paul" wrote:

Jerry: Thank you very much for the reply. I do not know Jim. I work in
North Carolina and am helping on a project trying to track red drum movements
in a saltwater estuary.

Since I am quite new to Access I am having some trouble following your
suggestion. I assume that F1 and F2 are different fish ID's? Also, are the
lines you sent me something that is a macro or that I build directly into a
query in design view?

Thanks again, Paul

"Jerry Whittle" wrote:

Do you happen to know Jim Widlak? He does similar stuff in Tennessee and
Kentucky. An old Air Force buddy of mine.

I can sing this song in one note! Something like the query below can find
the last place all the fish were. You'll have to put in the actual field
names and such. It also depends on the Date and Time fields being actual
Date/Time datatypes. Too bad that they aren't in one field as it would make
things much simpler and less prone to problems.

SELECT F1.FishID,
F1.Location,
F1.[DetectDate]+F1.[DetectTime] AS DateTimes
FROM tblFish AS F1
WHERE [F1].[DetectDate]+[F1].[DetectTime])
IN (SELECT Top 1
F2.[DetectDate]+ F2.[DetectTime]
FROM tblFish As F2
WHERE F1.FishID = F2.FishID
ORDER BY F2.[DetectDate]+ F2.[DetectTime] DESC)
ORDER BY F1.FishID;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Paul" wrote:

I have a table with 1.2 million records.
The table contains 4 pertinent columns: a fish electronic transmitter
number (let's consider this to be the fish's ID number); a location
where the fish is electronically detected; the date of detection, and;
the time of detection. In the river I am working
in, there are 162 fish with transmitters, and I need to know where
each of these fish is every couple of months. Thus, I have to query
the 1.2 million-record table 162 times every two
months to get updated data on each fish (right now I have one fish per
query, 162 queries). Each query by itself goes fast, but I have to repeat
this step
162 times. Rather than having to update each query one at
a time (time consuming), I have tried to write a macro that will allow me to
run all 162 queries at once every 60 days. However, I find that the macro
builder will allow me to only enter one query name at a time. Any solutions?

Thank you very much, Paul
.

Relevant Pages

  • RE: how do I update multiple queries at once?
    ... information in one query instead of 162 of them. ... It makes the statement shorter and helps as the SQL uses the same table ... Jerry Whittle, Microsoft Access MVP ... I assume that F1 and F2 are different fish ID's? ...
    (microsoft.public.access.gettingstarted)
  • RE: how do I update multiple queries at once?
    ... information in one query instead of 162 of them. ... Jerry Whittle, Microsoft Access MVP ... Open up one of your existing queries in design view. ... I assume that F1 and F2 are different fish ID's? ...
    (microsoft.public.access.gettingstarted)
  • RE: Assigning unique identifier to unique authors
    ... The SQL statement would look something like below. ... Books to your actual table name. ... Jerry Whittle, Microsoft Access MVP ... Create a query based on just the FirstName and LastName fields. ...
    (microsoft.public.access.queries)
  • RE: Query criteria repeating
    ... The SQL statement doesn't define the parameter. ... Jerry Whittle, Microsoft Access MVP ... same criteria twice before the query runs. ...
    (microsoft.public.access.queries)
  • RE: Subquery help
    ... one semicolon and that is at the very end of the SQL statement. ... shouldn't need one in the subquery. ... Jerry Whittle, Microsoft Access MVP ... The query should be bringing back multiple references between the specified ...
    (microsoft.public.access.queries)