I have a large database of contacts that I'm trying to build a user
friendly front for in Access 2000. Essentially I'm trying to build an
interface that allows a user to query contact position titles and then
narrow by several additional selects including states, whether or not a
contact has a mailing address, a phone number, and/or an email.

Using Jon K's post here (http://www.access-programmers.co.uk/forums/showthread.php?t=103312) helped me immensely with the initial groundwork,
however I find myself now stuck trying to build out a few more features.

Right now I have a form that looks like so:

(txtbox) Position Title 1:

(txtbox) Position Title 2:

(txtbox) Position Title 3:

(txtbox) Position Title 4:

(txtbox) Position Title 5:

(txtbox) Position Title 6:

(combo box) State:

(cmnd button) Preview


The two immediate query obstacles I have are

1.) The position title query language is set up so that entering no
information automatically brings back all records, however in the
situation where I enter fewer than 6 position titles, any blank boxes
cause all records to be returned. The only work around I've found is
entering a value like "x" in the otherwise blank boxes in order to
guarantee no results being returned.

2. )I would love the ability to select multiple states instead of a
single state. Is this something that can be addressed in the query or
the combo box?

I've attached my database below which I figured would be a more practical
way of demonstrating a complete picture of where I'm at.
Thanks in advance for any and all suggestions. This is my first post to
the forums after having found the site some months ago and utilizing many
of its' resources. Please let me know if this belongs in a different
forum or if I've forgotten a crucial peace of information.

-James

http://downloads.btabdevelopment.com/screenshots/welcometoawf.png

You really need to normalize your database first and then searching becomes a lot simpler. I think you should read this and then fix your database accordingly:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf
as you shouldn't have repeating fields (AdmDiv1, AdmDiv2, etc., JobTtl1, JobTtl2, etc.). You should have RECORDS for those and not columns for those.

You really need to normalize your database first and then searching becomes a lot simpler. I think you should read this and then fix your database accordingly:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf
as you shouldn't have repeating fields (AdmDiv1, AdmDiv2, etc., JobTtl1, JobTtl2, etc.). You should have RECORDS for those and not columns for those.

Hi Bob,

Thanks for the response. I'll take a look at the article you suggested. Regarding the AdmDiv and JobTtl columns - those are departmental and title hierarchies and the information therein is record specific.

Regarding the AdmDiv and JobTtl columns - those are departmental and title hierarchies and the information therein is record specific.
Regardless, when you have multiples, they should be a new record in a junction table and not multiple fields in the table.

Regardless, when you have multiples, they should be a new record in a junction table and not multiple fields in the table.

Just read through the first part of the article you linked to and I think I'm starting to understand your point. At this stage I won't be able to change the structure, but going forward it's definitely something I'm going to have to address.

Thanks for bringing that to my attention. :)