Multiple Dropdowns search Multiple Tables, to give 1 resultset

[DoubleHelix66]

Hi, I have a nasty search page to produce, and I have whacked my head against the wall one too many times.
So I'm looking for some expert assistance.

THE PROBLEM
A search page with multiple dropdown menus, (3 for this simplified example).

Dropdown 1 (Member State)
NSW
VIC
TAS

Dropdown 2 (Cats)
Siamese
Burmese
Manx

Dropdown 3 (Dogs)
Labrador
Greyhound
Bulldog

There are also 3 tables

Membership Table
MemberID | MemberName | MemberState
----------------------------------------

CatType Table
CatID | MemberID | CatType
----------------------------------------

DogType Table
DogID | MemberID | DogType
----------------------------------------

I need to create an SQL query that selects MemberID, MemberName and MemberState from Membership Table, and compare the MemberID to the Cat and Dog table.
Some members will have cats, some will have dogs and some will have both.
If a user selects only a state. all cat and dog owners in that state will be displayed. (The other two dropdowns will be ignored).
If a user selects a state and a cat type.... blah, blah, you can guess the rest....

I have considered unions of the memberID fields but I need the MemberName in the resulting recordset for displaying the result, and the other fields for the search.
I can join the tables OK but I get massive result sets.

I'm sure it can be done, But if it can't please let me know.
Any pointers, suggestions, snippets or big SQL chunks would be greatly appreciated.


Thanks
Simon.

 

[DanChard]

Hello - I can't throw enough time at this to set up all the tables as you've described and create the search form but the following SQL may be useful:

select *
from (select memberID, membername, memberstate, cats.cattype as animal_type, 'CAT' as pet_type
from membership inner join cats on membership.memberID = cats.memberID
union
select memberID, membername, memberstate, dogs.dogtype as animal_type, 'DOG' as pet_type
from membership inner join dogs on membership.memberID = dogs.memberID)
where (memberstate = [Forms]![Form1]![cmbo_memberstate]
or [Forms]![Form1]![cmbo_memberstate] = '(all)')
and ((animaltype = [Forms]![Form1]![cmbo_cattype] and animaltype = 'CAT')
or [Forms]![Form1]![cmbo_cattype] = '(all)')
and ((animaltype = [Forms]![Form1]![cmbo_dogtype] and animaltype = 'DOG')
or [Forms]![Form1]![cmbo_dogtype] = '(all)');

(I have assumed that your search form is called 'Form1' and that the combo boxes you are using to search are called 'cmbo_memberstate', 'cmbo_cattype' and 'cmbodogtype' appropriately. As these probably won't be the correct names you should update the SQL accordingly. I've also assumed that any combo box that you wish to be ignored in the search will store the value '(all)', this is just so that Access knows when to ignore particular boxes)

Create a query with this SQL as the source and create a button on your form which opens that query. When the query opens, it should look at the values in the combo boxes on your form and return only the results you'd expect.

Apologies for not being able to test this in any way but it should at least point you in the right direction!

Good luck,
Dan