Update Query

[zashaikh]

Hello,

I need some help running an update query. This is something I have never done before, and Im pretty scared about screwing up the data.

Basically, I have two tables. tblLocation has a column of cities and a column of states (for those cities).

I also have a table called tblMain. I recently added a state field (cboState) to tblMain. Now I have cboCity and CboState.

No data is filled in on tblMain concerning states. How do I make cboState fill in depending on what cboCity is on my tblMain? I need to somehow link tblLocation to it.

Any ideas? Ive never had to do an update query involving two tables.

[JoeM]

Is it your intention to get rid of the tblLocation table for good?
If not, then there really shouldn't be any need for an Update Query, as you can create a simply query linking your two tables on the State field, and returning everything that you need.
So it isn't really necessary to physically move the State value to your tblMain table if you are keeping the tblLocation table.

Out of curiosity, how are you accounting for City names that appear in multiple states (like Springfield, Washington, etc)? If the City names are not unique, that is going to present problems for you regardless of which method you elect.

[zashaikh]

I have no intention of getting rid of tblLocation. And I am able to create a query to return all that I want. But I want the data to be displayed directed on tblMain and frmMain.

[JoeM]

In a well-designed database, information is usually not duplicated/listed in more than one place. What many people new to Access and databases often don't realize that pretty much anything you use tables for (data sources for Forms, Reports, Data Exports, etc), you can also use a Query for. So, you should be able to create a query and use it as the data source of your frmMain.

The general rule of thumb is that anything that can be calculated "on-the-fly" should not be stored. It helps with data integrity, and prevents unnecessary maintenance.

For example, let's say that up have someone who lives in Austin, Texas. If you stored the state as a calculated field, if you change the value of Austin to something like Los Angeles, now the State value is going to be wrong, as it will still return Texas, unless someone also remembers to update that. However, if it is looking that value up from your other table, it will change automatically without any needed interaction.

So I would recommend creating a query and returning the state value either by a joining of the tables, or by using a DLOOKUP calculation.

[zashaikh]

Actually, in the example with Austin and Los Angeles you gave, I would not have that issue. If I changed city, it would automatically update the state due to this code:

Private Sub IDCity_AfterUpdate()
Me.State.Value = Me.City.Column(1)
End Sub
FYI, my record source for the field is SELECT [Locale].[City], [Locale].[State] FROM [Locale] ORDER BY [City];

[JoeM]

So, you have VBA code that is doing the lookup explicitly (which isn't really necessary, if you choose one of the other routes I suggested).
Also, note that only works for changes made within the Form. Changes due to queries, imports, or direct table edits would not be picked up by that.

Nevertheless, I said my piece. What you choose to do is up to you, though I think you should just be aware of best practices and potential pitfalls.
If you want to go the route of an Update Query, it would look something like this:

UPDATE tblMain 
INNER JOIN Locale 
ON tblMain.City = Locale.City 
SET tblMain.State = [Locale].[State]
WHERE tblMain.State Is Null;

Note that if your City name is not unique in your Locale table, you will probably run into issues with whatever route you choose, since your City to State relationship will be One-to-Many.

[zashaikh]

Thank you!!. That helped.

And I do appreciate the advice. The issue is that this database has been around for years. So to go around changing it is 1) not going to be easy 2) not something I am allowed to do. So I am ,in effect, trying to "get around" things.

But again, thank you. Much appreciated.

[JoeM]

And I do appreciate the advice. The issue is that this database has been around for years. So to go around changing it is 1) not going to be easy 2) not something I am allowed to do. So I am ,in effect, trying to "get around" things.

Understood. That is often the case in the business world!

Anyway, glad I was able to help you out.