Unique values from a query

[Tracy]

In my database I have people who are members of clubs. In a report I want to display "unique" people. So I want to run the report and say: give me all the people who are a member of club1 or club 2 or club3. So if Fred is a member of all three clubs I want his name to appear once, and so on.

So I have done a query and in the query properties set the return unique values to yes. I have club name in the query too, which I need to show in order to run the report for different combinations of clubs, but obviously if I have the club name in the query too and Fred is a member of all 3 clubs, the query will show Fred's name 3 times. So in the report his name will also appear three times.

Anyone have any ideas how I can include the clubname in the query but still only show unique names in the report?

To open the report I am using docmd.openreport from a form, with a where condition of "Club In(club1, club2,club3)"

Any help very much appreciated.
Thanks
Tracy

 

[dcx693]

Do you care which club name appears with a user if that user has multiple clubs? For example, if Fred is in club 1 and 2, do you care which one shows up?

You can create a totals query (from a normal select query, go to the View menu and choose Totals). In the Total line for the user name, choose Group By. In the total line for the clubname you can choose Max, Min, First, or Last.

 

[Jonno]

May I add - For the Club field you could set the criteria as WHERE - and as the criteria you could reference a textbox on your form, containing the chosen club. Then set the Reports Source as this query?

 

[Tracy]

Thanks for your help all sorted now.