Preventing duplicate records in query/database design?

Hi,

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

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=blue]
>
> 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]

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]