6 descriptive drop downs

  • Thread starter Thread starter loo
  • Start date Start date
L

loo

I have created a contact database for a user. They wanted to be able to
give descriptions on specialty to each contact via the use of drop down
boxes which will be searched on at a later date. They wanted drop down
boxes to eliminate the possibility that someone else might enter a
misspelled word and therefore not be able to search on it. How it is
currently set up a contact can have 1-5 predefined specialties which could
be in any order. The best I could come up with so far would be with logic
such as this logic:

if query1 (taken from user) == description1 OR query 1 ==
description2.......... OR description1 == description5

AND

query2 (taken from user does not equal * ) == description2 OR query2 ==
description3 ......etc.

AND keep cycling down until all the queries are met.

EXAMPLE:

if i want to find a contact that specializes in math, physics and
chemistry........... it is possible that the search criteria might by
physics, chemistry and math.......... I appreciate any suggestions. Thank
you.
 
I have created a contact database for a user. They wanted to be able to
give descriptions on specialty to each contact via the use of drop down
boxes which will be searched on at a later date. They wanted drop down
boxes to eliminate the possibility that someone else might enter a
misspelled word and therefore not be able to search on it. How it is
currently set up a contact can have 1-5 predefined specialties which could
be in any order. The best I could come up with so far would be with logic
such as this logic:

if query1 (taken from user) == description1 OR query 1 ==
description2.......... OR description1 == description5

AND

query2 (taken from user does not equal * ) == description2 OR query2 ==
description3 ......etc.

AND keep cycling down until all the queries are met.

EXAMPLE:

if i want to find a contact that specializes in math, physics and
chemistry........... it is possible that the search criteria might by
physics, chemistry and math.......... I appreciate any suggestions. Thank
you.
Click to expand...

Hi Loo,

Any time you are asking how to handle 5 things, 6 things, or any number
of things, you usually have an incorrect database design. In your case,
you should have three tables:

tblContact
- ContactKey
- ContactName
- etc.

tblSpecialty
- SpecialtyKey
- SpecialtyName

txrfContactSpecialty
- ContactKey
- SpecialtyKey

This third cross-reference table is what relates a contact to an
unlimited number of specialties. This would be maintained by the user
in a continuous subform, with a combobox on each row to pick the
specialties.

Then, querying becomes much easier. To find all Contacts with a
Specialty, you can use simple queries that join the tables together and
use a Where clause for the desired SpecialtyKey.

"Database developers know only three numbers: zero, one, and many."

Hope this helps,
 
Armen Stein said:
Hi Loo,

Any time you are asking how to handle 5 things, 6 things, or any number
of things, you usually have an incorrect database design. In your case,
you should have three tables:

tblContact
- ContactKey
- ContactName
- etc.

tblSpecialty
- SpecialtyKey
- SpecialtyName

txrfContactSpecialty
- ContactKey
- SpecialtyKey

This third cross-reference table is what relates a contact to an
unlimited number of specialties. This would be maintained by the user
in a continuous subform, with a combobox on each row to pick the
specialties.

Then, querying becomes much easier. To find all Contacts with a
Specialty, you can use simple queries that join the tables together and
use a Where clause for the desired SpecialtyKey.

"Database developers know only three numbers: zero, one, and many."

Hope this helps,
Click to expand...

Armen,
Thank you very much for your help, I will set this up today. Can I drop
another post to your attention if I have any problems? Have a nice day and
thanks again.
Loo
 
Hi Loo,

Yes, feel free to post back to this thread - I try to check back
regularly. There are lots of other people who are willing to help also.

You may also email me if you have a specific question.

Regards,
 
Back
Top