Jump to content

Value List Query

This topic is 6792 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have 2 databases

The first is a client base (Names, addresses etc)

The second is postal code base (Town, State, Postal Code)

I have set a relationship between DB1 and DB2 so that the town field in DB1 allows me to select a town and the State and Postal code automatically updates from DB2 into DB 1.

The problem is that when there is several towns with the same name (but in different states) it simply gives the first name on the list. I have used a value list for the town field in DB1 to show me the town names in DB2 and I modified this so that it also shows me the State when I drop down the list to search for a town in DB1. However, in the drop down list it still only shows one occurence of any town that is in more than one state and therefore I don't get the opportunity to select from all States for DB1.

Is there a way to get ALL entries from DB2 to show in my DB1 dropdown?

Link to comment
Share on other sites

Hi Roger,

A value list will always show only a single occurance of any value. Here's the setup I would recommend using, which will avoid this stumbling block. It makes data entry a little "unusual", in that you enter the state BEFORE you enter the town, but you'll see the advantage in a minute.

First, on your State field in DB1, have a drop down with the list of states from DB2.

Next, create a relationship from the State field in DB1 to the State field in DB2. Then, create another value list. Set it to be a value list using values from a field, and then choose the "Only related values" option and select the relationship you created. Then, select the Town field. So what you end up with is a value list which gets it's data from only those records in DB2 which are related to the State field in DB1 -- in other words, it'll show only a list of town within the state you've chosen. This is called a conditional value list; it's "conditional" on the state you choose.

Finally, here's how you'd pull in the correct postal code. You really need to find the postal code based on both state and town; a relationship just using town won't always get the correct one, as you found. Instead of using Town as your key field, you want to set up a calculated key field. In both files, set up a text calculation = State & " " & Town. So, your calculated field contains the state name and the town name separated with a space. Then, create a relationship from DB1 to DB2 using these calculated fields. That way, it will only match records where both the state and town match.

Link to comment
Share on other sites

Or alternatively, after the user selects the town, present them with a further choice of matching states and zips, using a conditional value list based on a relationship which matches town::town between your two dbs. wink.gif

Link to comment
Share on other sites

An alternative way is to force each value list item to be unique by combining it with a unique ID.

I had a similar situation where the user was selecting from a list of churches. (Do you have any idea how many St. John's there are?) Only the first instance of any church name was showing up.

Now in my case, what they were really selecting from the Value List was the ID# for the church. The church name was the "show also" field of the value list, and the value list was also set to sort by the church name. But even though each church had a unique ID, and the first field of the VL was the ID number, the value list was only showing unique church names.

My solution was to create a calc field:

ChurchVLName = ChurchName & "(" & ID & ")"

This gave each church a unique Name-ID combination. I set ChurchVLName as the 'show also' field for the value list. When it was selected, the user then saw something like:

0001 St. John's (0001)

0002 St. John's (0002)

0003 St. Mary's (0003)

If the user selected 0002, the filed contained 0002, of course. But it was placed underneath another field that displayed the church name based on the ID to Church relationship. So what they see in the end is "St. John's"

Good luck,


Link to comment
Share on other sites

This topic is 6792 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.