Use query or table to update another table

[fblanchard]

I have a table (the MAIN table) with station IDs and coordinates, as well as other information. The coordinate columns in this table (Latitude and Longitude) have some records with unknown coordinates. I queried out the Stations IDs with only unknown coordinates and grouped by. I would like to use this query to update the main table by typing in the new coordinates in the query and allowing it to populate the main table coordinates that match the same station IDs in the query.



I also converted the query into a table if it is easier to connect two tables with the same cirteria as above.

I hope this makes sense and would appreciate any advise.

Thank you.

[Bob McClellan]

I would like to use this query to update the main table by typing in the new coordinates in the query and allowing it to populate the main table coordinates that match the same station IDs in the query.

I also converted the query into a table if it is easier to connect two tables with the same cirteria as above.

If table2 is the target being updated and table1 has the values that you want
to update from... then something like this should work....
PLEASE BACKUP YOUR TABLES PRIOR TO TRYING YOUR UPDATE QUERY.

Update Table2
set Longitude = table1.Longitude,
Latitude = table1.Latitude
from Table2
inner join Table1 on Table2.[Station ID] = Table1.[Station ID]

hth,
..bob

[fblanchard]

Table 1 does not have the values in yet. I want to be able to enter it into table 1 as I get them and have it update table 2. Table 1 has all unique Station IDs (12) and table 2 has those same Station IDs duplicated (300,000+).

[Bob McClellan]

So... let's say you want to update the 300k rows in table 2 for [Station ID] = 1
After populating the long / lat in table 1 for [Station ID] 1, then run the query
with the additional parameter... where Table1.[Station ID] = 1

There are a number of ways to do this....
You could also have the params in the qbe grid in brackets. this will prompt you for the params when you run the query....

OR Disregard table 1 altogether and simply use a query that says...
Update Table2 set Longitude = [What is the longitude] where [Statioin ID] = [What Station ID should be updated?]

When you execute the query, it will prompt you with What is the longitude then it will prompt you with What Station ID should be updated? prior to executing the query.

hth,
..bob