Conditionally Update field in table with data from another table

[Ellette]

I am trying to create an update query that when the AcctNo in G1Table = the
AcctNo in FacilityTable (which here it is the primary key), then I want the
County ID and the Region number from the FacilityTable to populate the
corresponding fields County_ID and RegionNo in the G1Table.

All I get are rows of blanks...

Thanks!
Ellette

 

[John W. Vinson]

I am trying to create an update query that when the AcctNo in G1Table = the
AcctNo in FacilityTable (which here it is the primary key), then I want the
County ID and the Region number from the FacilityTable to populate the
corresponding fields County_ID and RegionNo in the G1Table.

All I get are rows of blanks...

Thanks!
Ellette

Why do you want to store this information redundantly in the second table? On
the face of it, this violates the very basic "Grandmother's Pantry Principle":
"A place - ONE place! - for everything, everything in its place". If you can
always look up the County ID and Region Number using a Query joining the two
tables on AcctNo, there is no benefit to copying them into the second table.

If I'm misunderstanding, please explain the rationale.

 

[John Spencer]

As to why all you get is rows of blanks.

If you are simply switching to datasheet view, then Access is showing you what
WILL be updated. If you actually want to update the fields, you must run the
query - Query: Run from the menu or close the query and then open it from the
query list to run it.

If that is not the problem, then open the query in SQL view and post the SQL
statement.

John is absolutely correct that it is not (normally) a good idea to store data
in more than one location.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County