Update Query Question

[mcl]

I have successfully used update queries to change a values to a specific new
value.

But I realized for the 1st time when I tried using it to update fields to
values in another table I linked it to that I couldn't get it to update.
What could I be doing wrong? The primary table has record high and low
temperature values by month for maybe 10000 weather stations around the
world. So each station has 2 values (high & low) for each of the 12 months.

Using ODBC I can link to our oracle tables which ingest weather data
everyday. Today is 29 March 2008. Data for yesterday would be available.
Pulling down the daily data for yesterday I can crunch the data and look for
stations where the records have been exceeded for that month. So on Thursday
27 Mar 2008 there were 75 stations with new record highs for March & 12
with new record lows. I use a "make table query" to compare the new data
with the my primary records table to build new tables. One table with new
highs and one with new lows. Why build tables you ask? Because I can then
use ArcGIS to link to those tables and display the data. (Did you know that
ArcGIS speaks MS Access very well. In fact ArcGIS uses MS Access as it's
personal Geodatabase format.) It's a good QC tool plus the temperature
records cluster. BTW, the 75 highs were mostly in SW Asia. Ok, then I
decided to try an update query where I used the original records list linked
by station number and month (3 the value in the new records table for March
obviously) to the new record high table to update those 75 stations to their
new values. But I couldn't get it to work. When I tested it only the old
values showed up.

 

[John W. Vinson]

But I realized for the 1st time when I tried using it to update fields to
values in another table I linked it to that I couldn't get it to update.
What could I be doing wrong? The primary table has record high and low
temperature values by month for maybe 10000 weather stations around the
world. So each station has 2 values (high & low) for each of the 12 months.

A two-table join will only be updateable if the record to be updated can be
uniquely identified... AND Access "knows" it by having an appropriate index.

THere are many reasons that a query might not be updateable; I'm GUESSING
(since you didn't post the query it's impossible to be sure) that you're
trying to update a Totals query. No such query is ever updateable (even though
logically it should be). You may need to update to a DMax() function call
instead.

You're making a table to export - are you certain that you can't export from a
Query? It would save you a step, and save your database some bloat.

 

[mcl]

I'm trying to update a table using the values in another table. They are
related by station number and month.

 

[John W. Vinson]

I'm trying to update a table using the values in another table. They are
related by station number and month.

Please post the SQL of the query. Do you have a unique Index on the joining
fields? That may be all you need...