Creating a look up table and update main table based on it

[sowatup]

Hi all,

I have a database for my church with about 1300 entries. I have a main table that looks like below:



We are now trying to categorize the entries by zone based on city. I have a list of cities and what zone they fall under. For example:

City XXX1 East
City XXX2 West
City XXX3 Remote

I want to do two things:

1. Create a look up table for city/zone
2. Then update the zone field in the main table based on the city in each entry

You would have to guide me step-by-step to do this as I am pretty illiterate when i comes to creating relationship tables and update queries.



Can you please help me out? Any other suggestions to improve this database design will be useful! Below is the screenshot of the form that previous owner created

[Bob Fitz]

Can you post a copy of the db

[sowatup]

I can't post a copy of the actual db because it has people's name, email addresses, physical addresses, etc. I van attach an empty version of the db, if that helps.

I went through like 400 entries and manually categorized zone based on the city. There has to be a better way to do this.

[Bob Fitz]

can you post a copy with a few dummy records to show the problem

[sowatup]

Here you go. Hope this helps.

I am trying to have a table that can automatically assign a zone based on the city, and have that information appear on the MainForm after City is typed.
If would also be nice to auto fill state based on the city. I only dealing with 3 three states, and have list of major cities in those states.

For example:

Happy Valley East
Portland East
The Dalles Remote
Salem Remote
Eugene Remote
Roseburg West
Albany West


Any other tips or trucks to normalize the db would be useful.

Thanks!

[bcmarshall]

The AfterUpdate event for City would trigger a DLookup and a SetValue for the Zone and State fields.

Create a table called Zones (or whatever else you want) with three fields, City, State, and Zone.

Now fill in the City, State, and Zone information for each location of interest into the table. You can add as many as you want.

Then AfterUpdate of City field would populate the State field with DLookup("[State]","[Zones]","[City]=[City]")
It would also populate the Zone field with DLookup("[Zone]","[Zones]","[City]=[City]")

[sowatup]

Thank you so much for the reply. I am still a bit confused. I created a new table with the three fields and populated it. I am still confused on how to link tblMandirDatabase with the new tblStateZone? I am also confused... err don't know how to update existing State and Zone fields in tblMandirDatabase? I don't know where I would put the:

Then AfterUpdate of City field would populate the State field with DLookup("[State]","[Zones]","[City]=[City]")
It would also populate the Zone field with DLookup("[Zone]","[Zones]","[City]=[City]")

I am so sorry, my Access skills are trash I got assigned this due to lack of resources and I really want to learn how to do this stuff too.

[bcmarshall]

Which version of Access are you using?

[sowatup]

2010, 64-bit. I think my biggest problem is I am still thinking of access as excel :/

[bcmarshall]

OK, let's start from the beginning. You may want to delete the existing City field and change it to a Combo Box. That will leave all the cities in a drop-down list. Those instructions are in red and are completely optional. If you prefer just skip to the black text.

Go to Design view, highlight and delete the City field, and then click the Design tab on the ribbon and select the Combo Box. Hover over the icons to read their names. It's like the 9th from the left. Click it, and then draw in a new field to replace the deleted City field.

As soon as you've done that a Wizard will pop up. Click "I want the combo box to look up values in a table", and then click Next.

Choose Tables, then select Zones.

Select the City field.

Select Ascending for sort order.

The last step is to name your field, likely City.

Now adjust the size of the field to fit your form properly.

Now select the City field with the Mouse and open the Properties box from the ribbon at top.

Go to the All tab and at the top of the list set the Control Source to City. Name the field City as well.

Select the City field. Click the Event tab, then go to After Update. Click on the far right side of the After Update Field and an ellipses (...) will be visible. Click that, and select Macro Builder.

In the ribbon at top of the window that opens, click Show All Actions.

In the Add New Action box in the Macro body, select SetValue. In the Item field that opens, type [Zone], the name of the field you want to populate. In the Expression field below it paste DLookup("[Zone]","[Zones]","[City]=[City]")

Select Add new Action again, SetValue, Item=[State], expression is DLookup("[State]","[Zones]","[City]=[City]")

Save and close the Macro, save the form and switch to Form View. Now test it. It should populate the fields you want. You can go back and add one more New Action, a GoToControl action, in order to move the cursor over the fields you're no longer manually populating, if you wish.

One more thing. Back to design view, open Properties Box, go to Events tab on the City field, go to On Dbl Click, and select the Macro Builder. Select OpenTable from the list of Actions, and enter the name of the table as Zones. Save macro, save form, and go back to Form View.

Now if you come across a new city that's not in your db you can just double-click the City field, open the table, add the new city, and close it and move on.

I've gotta add something here. Nobody but me would go to this much detail to answer a question for you by filling in the absolute basics, and you can't expect that you can come in with quite literally zero knowledge and prevail upon the good people here to answer your questions and hold your hand through every step. It's just not gonna happen. You're expected to have some understanding of Access. The people are very helpful and willing, but I think you understand where I'm coming from.

You need to start reading up and studying Access. Use the Online Help, and beat your brains out a bit like we all have done.

Good luck.

[sowatup]

First off, thank you so much. I really appreciate you going into the details and walking me through the process. I have been going through build in examples and disecting them, and just learning about the basics and 1-1 and 1-many relationships. Not saying I won't read up on it, but I think reading from someone who has gone through the learning process makes a difference, and I really appreciate you going into the details. Makes a wored of a difference.

Thanks!

[bcmarshall]

YW. We've all been there, and I know there have been many, many times that I really appreciated the advice of others. But if you're gonna do this you've got to start learning. You can't confuse this with Excel.

Good luck.