Filtering possibly?

[Neil Greenough]

I am currently putting together a contact's database which will hold contact
details for over 400 people.

Each contact falls into a working city (ie. Liverpool, Manchester, New York)
etc... Within this City, they will then fall into a specific department. The
departments vary from city to city.

Now, I am assuming that I have to create a table listing the different
cities and the different departments. Then, from there, two lookup parts in
my contacts table, one to look up city, and the other to look up department.

My question is if i select Manchester, how do i get the corresponding
department to show in the next lookup form (and not the options for New York
for example?) Also, how do I go about structuring my tables. What tables
should I create? An individual one for each city listing it's departments?
What do I need to include?

Thanks

 

[Guest]

I'll assume that there will be more than one department per city, or you
could just put the department with the city record.

Assuming that you have the two tables you described:

City Department
=== ========
CityCode DeptCode
CityDesc DeptDesc

Etc. create a thrid table

CityDept
======
CityCode
DeptCode

Where the codes relate back to the same fields in your original table, on
row in CityDept for each legitimate combination. From there, populate your
table and build a query to pull in the requisite data to display the
departments (e.g. join with Departments). Set the criteria on the city code
for this query to equal the city code that you are interested in (e.g.
identify a control on a form that contains the data) and if needed add code
to either control to requery the department when the city changes. (You
might try omitting the event code to see if it will update automatically,
right now I'm having a brain f*%t on whether or not it will)

Hope that helps.

 

[Guest]

I guess you are thinking of "Cascaded ComboBox" and in this case, you need to
requery the 2nd ComboBox (Dept) on the AfterUpdate Event of the 1st ComboBox
(City).

 

[Neil Greenough]

Van,

So in my new table I have CityID and DeptID, what info needs to go into
these? Is it lookup information from the other tables?

 

[Neil Greenough]

Right,

I have built my third table called CityDept and set an automated primaryID.
I then have my city which looks up the city from the city table for me and
then have the dept which looks up a department from the department table. I
have then matched all of these up.

Now, I ma confused as to the query bit etc... Could you go into more detail?
What are the next steps I need to take now?

 

[Van T. Dinh]

Personally, I would name them [frg_CityID] & [frg_DeptID] to indicate that
they are ForeignKeys to distinguish them from PrimaryKeys. In my databases,
only numeric PrimaryKey Fields have names ending in "ID".

Yes, they are linking Fields being used for look-up but don't use Access
LookUp Field. See The Access Web article:

http://www.mvps.org/access/lookupfields.htm

 

[Van T. Dinh]

This is what I meant in my first post about "Cascaded ComBoxes".

Basically on your Form for Contacts, you use 2 ComboBoxes [cboCity] and
[cboDept] and the RowSource of the [cboDept] is a parametrised Query that
depends on the value selected in the first ComboBox.

For example, see The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm