Using a query to filter - too many results

[pbouk]

Hi there,
I have a Contacts database with a form and combo-boxes that enable my user to filter and create custom lists.
They're working well, except I would like to fine tune the query the filters are based on.
I have five search filters (they do not cross filter, only one can be searched at a time)
Company
Category
City
State
Country

Basically, most contacts have one or more addresses and also are categorised by one or more "Category types". eg: Museum, Artist etc.
What I'm finding is that when I do a search (eg: Texas) it lists each contact in Texas many times, depending on how many addresses and categories it has associated with the name.
Some contacts are listed four times, because they have two addresses and two category types.



I'm not sure how to resolve this. Any ideas?
Query is below, and I removed Categories to see if it made any difference, but it's still listing each contact according to how many categories it's been typed as (as well as by the number of addresses)

[June7]

That is the nature of 1-to-many related data.

What is the query used for? Is it the basis for a report?

[pbouk]

Yes, a report or a mailing list.
Will my user have to sort through it to remove the duplicates or is there another way to handle it?

[June7]

That's what happens when you include multiple 'many' tables in the same query. Constraining the recordset to prevent multiple mailings to the same contact probably won't be easy. Will the mailing be to all contacts? Or only to contacts within a selected category? What should determine which address to use?

[pbouk]

Thanks, yes, I'll need to restrict the data to a mailing address I think.
I'm having the same problem with my multi-search box.

Is there any way to get the data base to search all addresses but only list the address types I specify?

thanks again

[orange]

Yes, but it starts with table design, normalization and relationships.
Why are there duplicates?

[pbouk]

Pretty sure my table design is ok. They're not duplicates, just repeats of the same info with the category/address fields field info different. So if there are two addresses and two categories for a contact, the contact appears four times.
I have a filter for making lists, and it won't be used that much and the record set is small, so it's not really such a big deal, but it is important for my keyword multi search.
Each of the results that come up can be clicked on and the user goes to the form where all the info resides. What I'd like is that the DB searches everything but only shows the mailing or Business or Home address (not all contacts have a mailing address).
Then, maybe I could add another search box for Categories, where the user could choose a particular category as well to further restrict the search.

Would that work?

[orange]

Can you post a copy of your database - remove anything confidential - to show the specific issue?
And tell us specifically what we should look for.

Or you could give us a sample or two using some of your data.

[June7]

Seems that query you already have will afford that search. Just have to be aware that unfiltered dataset will show repetition of contact name.

[orange]

Seems June7 understands your situation and is helping -- so I will bow out.