Preventing duplicate records in query/database design?

[Allen Anderson]

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

[Allen Browne]

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" <al***********@ mindspring.com> wrote in message
news:b8******** ********@newsre ad3.news.pas.ea rthlink.net...

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

[Allen Anderson]

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" <Al*********@Se eSig.Invalid> wrote in message
news:44******** *************** @per-qv1-newsreader-01.iinet.net.au ...

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" <al***********@ mindspring.com> wrote in message
news:b8******** ********@newsre ad3.news.pas.ea rthlink.net...

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