Update Query and Dlookup - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Update Query and Dlookup

William McDonald

3 years ago

I have imported a list of projects from Excel into a ProjectTable in Access Database. The imported data has the project location/city but not the state.

I want to update the project table to add the state. There are hundreds of projects in the list such that adding the state by hand impractical.

I've created city/location and State tables that can be used using dlookup. I still struggle understanding the workings of Dlookup even after watching Rich's Dlookup video.
ProjectTable includes fields for LocationName, StateName, and a StateID
LocationTable has LocationName, LocationID, and StatID
StateTable has StateName and StatID

Is there an easier way to finish filling out the ProjectTable besides an Update Query?

	Kevin Yip @Reply 3 years ago
Some city names may exist in multiple states -- Stanford is in CA, ID, IL, etc. And some cities are also known by different names. So, if your table only has city names, you may have a hard time matching them to states. This is real-world problem, and not really an Access or database problem.

	William McDonald OP @Reply 3 years ago
That is definitely a potential real world problem. It is also a problem I'm not concerned about in my database as each location name is also unique as I am working within a limited area of the country. Where the "location" includes a city name, a prefix is used to denote the difference in cities.

	Kevin Yip @Reply 3 years ago
Are zip codes used anywhere in your data? If it's a limited area, you may be able to find listings of city/zip on the Internet, which should help you in your effort: https://www.zipcode.com.ng/2022/06/list-of-idaho-zip-codes.html

William McDonald OP @Reply

3 years ago

No zip codes. My "locations" are more like campuses, and each location has a unique name even if two locations are in the same city.

On the positive side I found a work around whilst watching Rich's Import Changes video. My work around modified the source Excel file to include state and stateID columns; created a couple "lookup" tables for the state and state ID (there weren't too large to input by hand); Vlookup to fill in the state and stateID in Excel; then follow Rich's method to import and update product data in his video, only I updated the state and stateID.

So I don't have to redo this every other week, this database in intended to replace the Excel file.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View