Prevent showing duplicate records in a query

[suffrinmick]

Hi everyone!

I (Along with the help of some of you guys!) have managed to get a
query working which takes multiple values (types of organisation) from
a list box.

The query returns a list of contacts (tblContacts.ContactID, LastName
etc. ) who are linked to these particular organisations.

Some of the contacts are linked to more than one organisation and
therefore show up on the list more than once.

What do I need to do so that any of the contacts only display once in
the list. I've tried changing the UniqueRecords and UniqueValues
property settings of the query but it doesn't seem to work.
Thanks
Suffrinmick

[Rick Brandt]

<su*********@silvercookie.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi everyone!

I (Along with the help of some of you guys!) have managed to get a
query working which takes multiple values (types of organisation) from
a list box.

The query returns a list of contacts (tblContacts.ContactID, LastName
etc. ) who are linked to these particular organisations.

Some of the contacts are linked to more than one organisation and
therefore show up on the list more than once.

What do I need to do so that any of the contacts only display once in
the list. I've tried changing the UniqueRecords and UniqueValues
property settings of the query but it doesn't seem to work.

Unique Values should do it. Of course it only block duplicates consisting
of ALL columns in the output. If you have two rows with the same Names,
but different values in the other columns then you will still see the name
more than once.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Wayne Morgan]

Unique Values returns unique records based on the fields displayed in the
query's output. Unique Records displays unique records based on all fields
in the underlying tables. You may need to use two queries to get what you
want or, perhaps, while linking on the OrganizationID, don't include it or
any of the Organization information in the output of the query (uncheck the
Show box).

Example, this will filter on the date in Table2.Field2, but will only show
Unique Values from the fields selected in Table1. As you can see,
Table2.Field2 isn't listed as an "output" field at the start of the
statement. It is only used in the WHERE clause.

SELECT DISTINCT Table1.Test1, Table1.Test2
FROM Table1 INNER JOIN Table2 ON Table1.Test1 = Table2.Field1
WHERE (((Table2.Field2) Between #1/1/2000# And #12/31/2004#));

--
Wayne Morgan
MS Access MVP
<su*********@silvercookie.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi everyone!

I (Along with the help of some of you guys!) have managed to get a
query working which takes multiple values (types of organisation) from
a list box.

The query returns a list of contacts (tblContacts.ContactID, LastName
etc. ) who are linked to these particular organisations.

Some of the contacts are linked to more than one organisation and
therefore show up on the list more than once.

What do I need to do so that any of the contacts only display once in
the list. I've tried changing the UniqueRecords and UniqueValues
property settings of the query but it doesn't seem to work.
Thanks
Suffrinmick

[suffrinmick]

Wayne Morgan wrote:

Unique Values returns unique records based on the fields displayed in the query's output. Unique Records displays unique records based on all fields in the underlying tables. You may need to use two queries to get what you want or, perhaps, while linking on the OrganizationID, don't include it or any of the Organization information in the output of the query (uncheck the Show box).

Example, this will filter on the date in Table2.Field2, but will only show Unique Values from the fields selected in Table1. As you can see,
Table2.Field2 isn't listed as an "output" field at the start of the
statement. It is only used in the WHERE clause.

SELECT DISTINCT Table1.Test1, Table1.Test2
FROM Table1 INNER JOIN Table2 ON Table1.Test1 = Table2.Field1
WHERE (((Table2.Field2) Between #1/1/2000# And #12/31/2004#));

--
Wayne Morgan
MS Access MVP
<su*********@silvercookie.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi everyone!

I (Along with the help of some of you guys!) have managed to get a
query working which takes multiple values (types of organisation) from a list box.

The query returns a list of contacts (tblContacts.ContactID, LastName etc. ) who are linked to these particular organisations.

Some of the contacts are linked to more than one organisation and
therefore show up on the list more than once.

What do I need to do so that any of the contacts only display once in the list. I've tried changing the UniqueRecords and UniqueValues
property settings of the query but it doesn't seem to work.
Thanks
Suffrinmick

[suffrinmick]

I've managed to figure it out:

Select DISTINCTROW tblContacts.*, tblOrgTypes.OrgType,
tblPositions.Position, tblPositions.Organisation FROM tblOrgTypes INNER
JOIN (tblPositions INNER JOIN tblContacts ON tblPositions.Contact =
tblContacts.ContactID) ON tblOrgTypes.OrgTypeID = tblPositions.OrgType
Where [tblPositions.OrgType] In(" & Criteria & ");
Thanks Wayne and Rick

Suffrinmick