Update a column in table

[KenNJ]

Hi, all.
First time here, and I have a problem I hope the forum can help me with.
I have a table of project locations to which I've added a column for the county in which the site is located.
I also have a table of Counties that includes County Name, City Name, and State ID.
I'm trying to update the county field (which is currently empty) in the locations table with the appropriate county name where the City and State match in both tables.
I feel like I should know how to do this, but nothing I've tried is working.
If anyone can help me with this, I will be eternally grateful.
Thanks in advance.

 

[The_Doc_Man]

Something SIMILAR to

UPDATE Projects AS P INNER JOIN Counties AS C
ON ( P.City = C.City ) AND ( P.State = C.State )
SET P.County = C.County ;

Note that spellings and included spaces would have to match between the Projects and Counties table.

 

[KenNJ]

This is the SQL statement generated by Access for the query I entered.
UPDATE tblLocations
INNER JOIN tblCounties ON tblLocations.[LocCity] = tblCounties.[CntyCity]
SET
tblLocations.LocCnty = [tblCounties].[CntyName]
WHERE
(
(
[tblLocations].[LocCity] = [tblCounties].[CntyCity]
AND [tblCounties].[CntyStId] = [tblLocations].[LocState]
)
);

 

[cheekybuddha]

I'm trying to update the county field (which is currently empty) in the locations table with the appropriate county name where the City and State match in both tables.

Don't do this.

Get rid of the County field/column in your locations table

Add a 'junction table', eg LocationCounties, with LocationID and CountyID (the pk's from each of the other tables).

Or more simple, just have a saved query:

SELECT
  l.*,
  c.City,
  c.State
FROM Locations l
LEFT JOIN Counties c
       ON l.City = c.City
      AND l.State = c.State
;

Edit: Using your table/field names:

SELECT
  l.*,
  c.CntyCity,
  c.CntyName
FROM tblLocations l
LEFT JOIN tblCounties c
       ON l.LocCity = c.CntyCity
      AND l.LocState = c.CntyStId
;

 

[plog]

I also have a table of Counties that includes County Name, City Name, and State ID.
I'm trying to update the county field (which is currently empty) in the locations table with the appropriate county name where the City and State match in both tables.

1. City and state are not enough to identify county. Atlanta is in Fulton and Dekalb, Oklahoma City spans 3.

2. Add a county ID to the Counties table, update Locations to the proper CountyID and then remove City/State from the Locations table. You shouldn't store duplicate data in multiple places. Now, since it's in there and you need it for identification purposes, keep it until you have properly added that countyID data to Locations then remove it.

 

[The_Doc_Man]

Whoops, good point guys. Kansas City in the USA not only spans different counties, it is in two different states.

The syntax I gave you would have worked, perhaps, but it was answering a different question.

 

[cheekybuddha]

You shouldn't store duplicate data in multiple places.

This is the most important point.

 

[mike60smart]

This is the SQL statement generated by Access for the query I entered.
UPDATE tblLocations
INNER JOIN tblCounties ON tblLocations.[LocCity] = tblCounties.[CntyCity]
SET
tblLocations.LocCnty = [tblCounties].[CntyName]
WHERE
(
(
[tblLocations].[LocCity] = [tblCounties].[CntyCity]
AND [tblCounties].[CntyStId] = [tblLocations].[LocState]
)
);

Are you able to upload a copy of the database?

 

[Ken Sheridan]

I'm trying to update the county field (which is currently empty) in the locations table with the appropriate county name where the City and State match in both tables.

As a city in the USA can apparently be in more than one county, a Locations table should include CityID and CountyID foreign key columns referencing the composite primary key of a CountyCities table which models the binary relationship type between Cities and Counties. A Counties table would include a StateID foreign key column referencing the primary key of States. Each location in a city which spans two or more counties would then map to whichever county that location in the city is situated in, and to whichever state that county is in.

The locations table should not include a state column. To include a state in the table would introduce transitive dependencies, and the table would not be normalized to Third Normal Form, with the consequent risk of update anomalies.

I don't know of any towns in the UK which are currently in multiple counties, administratively rather than geographically speaking, but the town of Tamworth in Staffordshire was at one time partly in Staffordshire and partly in Warwickshire. The county boundary ran down the middle of the main street. It had two town halls and returned two members of parliament, despite being one of the smallest towns in England. In the Anglo Saxon period Tamworth was the de facto capital of the kingdom of Mercia, and its is an historical oddity that a county of Tamworthshire was never created.

 

[GPGeorge]

There is a small town in western Wyoming/eastern Idaho which is in two counties and two states. Main Street is right on the state line. Going north in Wyoming you pass cars going south in Idaho, assuming someone else actually happens to be out dragging main at the same time. The name of the town is, fittingly, Freedom.

 

[KitaYama]

I liked how @Ken Sheridan managed contacts location in this post.
He has Countries, Regions and Cities tables, all one to many relationship, and then saves only CityID for the contact. It covers up all possible duplicates.
In Contacts form, CityID combo is linked to the underlying table, and Region is filled based on the value of City combobox.

 

[Ken Sheridan]

I liked how @Ken Sheridan managed contacts location in this post.

Thanks. The_Doc_Man has now put the cat amongst the pigeons though, by pointing out that in the USA a city could be in more than one state. I'll need to revisit my demo to see how that can be handled using correlated combo boxes. As well as the CityID the Contacts table will need to include a RegionID column. The form would be unchanged in appearance as only one region would be selected, that in which the part of the city where the contact is located is situated, but selecting the city would also insert the RegionID of the selected region into the row in Contacts.

 

[KitaYama]

Thanks. The_Doc_Man has now put the cat amongst the pigeons though, by pointing out that in the USA a city could be in more than one state. I'll need to revisit my demo to see how that can be handled using correlated combo boxes. As well as the CityID the Contacts table will need to include a RegionID column. The form would be unchanged in appearance as only one region would be selected, that in which the part of the city where the contact is located is situated, but selecting the city would also insert the RegionID of the selected region into the row in Contacts.

If there's no duplicate city names in a region(State), your current file works perfectly.
If there are two cities with the same name in a region (State) in the same country, which I think is impossible, your solution in that file will fail.

 

[GPGeorge]

I don't think you can count on universal uniqueness among place names, even within a region. It's probably more or less true in general, but building a solution around that assumption is subject to clashes with reality. Like most database design decisions, I'd come down on the side of requiring unambiguous data classification rather than relying on continued good luck.

Most common patriotic city and town names across the USA

The following list identifies the most common patriotic place names across the United States. It does not include places that are named for people such as George Washington or Abraham Lincoln. Of a…

panethos.wordpress.com

 

[KitaYama]

Most common patriotic city and town names across the USA

The following list identifies the most common patriotic place names across the United States. It does not include places that are named for people such as George Washington or Abraham Lincoln. Of a…

panethos.wordpress.com

But those duplicate city names are in different region (States). I can not find a state that has two cities with the same name.
I'm not American, and I'm not sure if my assumption is correct, But it's what chatgpt is telling me

In the United States, you generally cannot have two incorporated cities with the exact same name within the same state. State laws prevent duplicate municipality names to avoid confusion in governance, mail delivery, and emergency services.

I also assume this is the same in other countries. Chatgpt couldn't find the same city name in the same prefecture globally. (I understand, chatty may be wrong).

 

[Ken Sheridan]

I also assume this is the same in other countries.

Not in the UK. The administrative unit below District (or County where the county is a unitary authority and therefore has no Districts) is Civil Parish. As these have defined boundaries, in formal contexts, such as my own former work in environmental planning, these are used rather than imprecise locations like 'town' or 'village' which might be in use in other contexts. Civil Parish names can legitimately be duplicated within a County, the two civil parishes named Stretton in Staffordshire being an example.

This is not currently a problem in databases as two civil parishes of the same name cannot exist in the same District as far as I'm aware. However, Districts will be abolished in the next few years, so having two Stettons in Staffordshire will become a problem, as they will no longer have separate DistrictID foreign keys. They will have separate ParishID primary key values of course, but these have no semantic significance, and it will no longer be possible to ensure that the right one is selected on the basis of the name alone, as can be done now by using correlated combo boxes where the District has to be selected first in an unbound combo box to restrict a bound Parish combo box's list to those in that district.

 

[Ken Sheridan]

If there are two cities with the same name in a region (State) in the same country, which I think is impossible, your solution in that file will fail.

For the purposes of my demo I've assumed that not to be the case. In reality I think it's an unwarranted assumption. I believe that there is more than one Springfield in at least one US state for instance. The solution would be to introduce County into the hierarchy of course, and to model the binary relationship type between cities and counties to which The_Doc_Man has alerted us by a table which resolves it into two unary relationship types. Which is way I shall be revisiting my correlated combo boxes demo.

 

[KitaYama]

For the purposes of my demo I've assumed that not to be the case. In reality I think it's an unwarranted assumption. I believe that there is more than one Springfield in at least one US state for instance. The solution would be to introduce County into the hierarchy of course, and to model the binary relationship type between cities and counties to which The_Doc_Man has alerted us by a table which resolves it into two unary relationship types. Which is way I shall be revisiting my correlated combo boxes demo.

I'll be waiting how your next version will be. I wonder if I can use that, because we don't have "county" here.
Our structure is Country->Prefecture (State)->City.

 

[Ken Sheridan]

I'll be waiting how your next version will be. I wonder if I can use that, because we don't have "county" here.
Our structure is Country->Prefecture (State)->City.

You should be able to adapt it. I deliberately used the generic term 'region', which could be a state in the USA, a county here in the UK, or a prefecture in your case. While the amendments I'm proposing will cater for a binary relationship type between cities and regions, it would still work for countries where the relationship type is unary.

Hopefully I'll be able to post an amended file here before very long.