Thank you Allen.
Yes. After posting my question I noticed that one of the secondary tables
had a "shown" field, putting it into the SELECT clause as well as the WHERE
clause. Removing that field fixed my problem.
Thanks for your feedback on the data structure.
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:446ae738$0$26966$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> Your data structure sounds perfect:
> - a table of people;
> - a table of roles;
> - a junction table with just the PersonID and RoleID foreign keys, to
> define the roles each person has.
>
> The query should work with DISTINCT, as long as you output fields from the
> Person table only.
>
> Obviously if you output the roles as well, then the person can turn up in
> multiple records, since the query perceives the role as different when it
> sorts out the DISTINCT part.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users -
http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Allen Anderson" <allenanderson@mindspring.com> wrote in message
> news:b8Aag.3155$x4.1046@newsread3.news.pas.earthli nk.net...[color=green]
>>
>> I'm trying to design contact (names and addresses) tables in an Access
>> database. Some of the contacts represent vendors, some are board members
>> of the organization, some are donors, some are neighbors of the
>> organization, some are politicians, etc. Rather than create separate
>> tables for each type of contact, I thought it would be better to have:
>>
>> one table with names/addresses
>> one table with kinds of lists (vendors, board members, donors, neighbors,
>> politicians, volunteers, etc) and
>> one table with list details (which combine IDs of names/addresses with
>> IDs of kinds of lists).
>>
>> This way a person/company could be on more than one list at a time, by
>> having multiple records in the list details table.
>>
>> My problem occurs when I want to prevent the same name/address records
>> from appearing multiple times when I run queries. For instance, a board
>> member may also be a member of the community, and thus have 2 records in
>> the list details table. I want to create mailing labels from a query
>> that includes people who are either board members or live in the
>> community, but I don't want 2 labels for the same person. DISTINCT and
>> DISTINCTROW are not working for me. Can you help?
>>
>> Thanks,
>> Allen[/color]
>
>[/color]
