Problem with duplicate records in a Mail Label Report

[Mike Webb]

Using Access 2002. Novice experience level.

Background: I created a query (qryMailingLabels) to pull together data for
the labels. I have three tables the query is based on: a Contacts table
(name, address, etc.), a Description table (locally assigned that describe
their category, such as "donor", "US Fish & Wildlife Service", "Christmas
Card sent", etc. - a Contact may have 5-10 Descriptions), and a Join table.
I did this with the idea that it'd be nice to sort the list to do labels for
just the Description needed.

The Problem: The query has almost 3500 records, but I only have ~1800
Contacts. Some of the Contacts are listed multiple times as they have two
or more assigned Descriptions. How can I enable the user to sort the list
prior to creating the report? I've looked at Expressions and SQL SELECT
statements in Access Help; couldn't find the answer.

TIA,
Mike

 

[Joan Wild]

Try changing the Unique Values property of your query to Yes.

Otherwise, post the SQL of your query.

 

[Mike Webb]

I don't know what you mean on the Unique Values of my query, so have posted
the SQL below:

SELECT [tblAddressList.TitlePrefix] & ' ' & [tblAddressList.FirstName] & ' '
& [tblAddressList.MiddleInitialName] & ' ' & [tblAddressList.LastName] AS
FullName, tblAddressList.Organization, tblAddressList.Address,
[tblAddressList.City] & ' ' & [tblAddressList.State] & ' ' &
[tblAddressList.ZipCode] AS CityStateZip,
tblAddressListCategories.Description
FROM tblAddressList
INNER JOIN (tblAddressListCategories INNER JOIN tblJoinContactsAndCategories
ON
tblAddressListCategories.SubCategory_ID=tblJoinContactsAndCategories.SubCate
gory_ID) ON tblAddressList.ID=tblJoinContactsAndCategories.ContactAddress_ID
ORDER BY tblAddressList.LastName;


Mike

 

[Joan Wild]

I've re-read your post, and I see that you want to do labels for 'just the
description needed'. You are getting multiple people listed because they
have multiple subcategories. If you just want to list for one of the
subcategories, you would use a where clause:

SELECT [TitlePrefix] & ' ' & [FirstName] & ' ' & [MiddleInitialName] & ' ' &
[LastName] AS FullName, tblAddressList.Organization, tblAddressList.Address,
[tblAddressList].[City] & ' ' & [tblAddressList].[State] & ' ' &
[tblAddressList].[ZipCode] AS CityStateZip
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE (((tblAddressListCategories.Description)=[Enter Description]))
ORDER BY tblAddressList.LastName;

--
Joan Wild
Microsoft Access MVP

I don't know what you mean on the Unique Values of my query, so have posted
the SQL below:

SELECT [tblAddressList.TitlePrefix] & ' ' & [tblAddressList.FirstName] & ' '
& [tblAddressList.MiddleInitialName] & ' ' & [tblAddressList.LastName] AS
FullName, tblAddressList.Organization, tblAddressList.Address,
[tblAddressList.City] & ' ' & [tblAddressList.State] & ' ' &
[tblAddressList.ZipCode] AS CityStateZip,
tblAddressListCategories.Description
FROM tblAddressList
INNER JOIN (tblAddressListCategories INNER JOIN tblJoinContactsAndCategories

ON

tblAddressListCategories.SubCategory_ID=tblJoinContactsAndCategories.SubCate
gory_ID) ON tblAddressList.ID=tblJoinContactsAndCategories.ContactAddress_ID
ORDER BY tblAddressList.LastName;


Mike

Try changing the Unique Values property of your query to Yes.

Otherwise, post the SQL of your query.

--
Joan Wild
Microsoft Access MVP

data
for labels
for

 

[Mike Webb]

Ah ... Can I carry that one step further and tie it to a combo box? I have
roughly 80 categories/descriptions.

I've re-read your post, and I see that you want to do labels for 'just the
description needed'. You are getting multiple people listed because they
have multiple subcategories. If you just want to list for one of the
subcategories, you would use a where clause:

SELECT [TitlePrefix] & ' ' & [FirstName] & ' ' & [MiddleInitialName] & ' ' &
[LastName] AS FullName, tblAddressList.Organization, tblAddressList.Address,
[tblAddressList].[City] & ' ' & [tblAddressList].[State] & ' ' &
[tblAddressList].[ZipCode] AS CityStateZip
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE (((tblAddressListCategories.Description)=[Enter Description]))
ORDER BY tblAddressList.LastName;

--
Joan Wild
Microsoft Access MVP

I don't know what you mean on the Unique Values of my query, so have posted
the SQL below:

SELECT [tblAddressList.TitlePrefix] & ' ' & [tblAddressList.FirstName] &

'

'
& [tblAddressList.MiddleInitialName] & ' ' & [tblAddressList.LastName] AS
FullName, tblAddressList.Organization, tblAddressList.Address,
[tblAddressList.City] & ' ' & [tblAddressList.State] & ' ' &
[tblAddressList.ZipCode] AS CityStateZip,
tblAddressListCategories.Description
FROM tblAddressList
INNER JOIN (tblAddressListCategories INNER JOIN tblJoinContactsAndCategories

ON

tblAddressListCategories.SubCategory_ID=tblJoinContactsAndCategories.SubCate

gory_ID) ON tblAddressList.ID=tblJoinContactsAndCategories.ContactAddress_ID
ORDER BY tblAddressList.LastName;


Mike

have
two the
list

 

[Joan Wild]

Yes you can. You can't manipulate the popup dialog in the query, but you
can create your own form to get the parameter.

Create an unbound form and add an unbound combo box. The row source of the
combo would be your descriptions (the wizard will build it for you).

Change the WHERE clause of your query to refer to this combo box i.e.

WHERE (((tblAddressListCategories.Description)=[Forms]![MyForm]![MyCombo]))

You would substitute the name of your form for MyForm and the name of your
combo for MyCombo.

The form would need to be open when you run the query. I presume you'll be
printing a report of labels. You can base your report on the query. Add a
button to your form to open the report.
--
Joan Wild
Microsoft Access MVP

Ah ... Can I carry that one step further and tie it to a combo box? I have
roughly 80 categories/descriptions.

I've re-read your post, and I see that you want to do labels for 'just the
description needed'. You are getting multiple people listed because they
have multiple subcategories. If you just want to list for one of the
subcategories, you would use a where clause:

SELECT [TitlePrefix] & ' ' & [FirstName] & ' ' & [MiddleInitialName] & '

'

&
[LastName] AS FullName, tblAddressList.Organization, tblAddressList.Address,
[tblAddressList].[City] & ' ' & [tblAddressList].[State] & ' ' &
[tblAddressList].[ZipCode] AS CityStateZip
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON tblAddressListCategories.SubCategory_ID =
tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE (((tblAddressListCategories.Description)=[Enter Description]))
ORDER BY tblAddressList.LastName;

 

[Mike Webb]

Outstanding! Thanks!

Yes you can. You can't manipulate the popup dialog in the query, but you
can create your own form to get the parameter.

Create an unbound form and add an unbound combo box. The row source of the
combo would be your descriptions (the wizard will build it for you).

Change the WHERE clause of your query to refer to this combo box i.e.

WHERE (((tblAddressListCategories.Description)=[Forms]![MyForm]![MyCombo]))

You would substitute the name of your form for MyForm and the name of your
combo for MyCombo.

The form would need to be open when you run the query. I presume you'll be
printing a report of labels. You can base your report on the query. Add a
button to your form to open the report.
--
Joan Wild
Microsoft Access MVP

Ah ... Can I carry that one step further and tie it to a combo box? I have
roughly 80 categories/descriptions.

I've re-read your post, and I see that you want to do labels for 'just the
description needed'. You are getting multiple people listed because they
have multiple subcategories. If you just want to list for one of the
subcategories, you would use a where clause:

SELECT [TitlePrefix] & ' ' & [FirstName] & ' ' & [MiddleInitialName] &

'

'

&
[LastName] AS FullName, tblAddressList.Organization, tblAddressList.Address,
[tblAddressList].[City] & ' ' & [tblAddressList].[State] & ' ' &
[tblAddressList].[ZipCode] AS CityStateZip
FROM tblAddressList INNER JOIN (tblAddressListCategories INNER JOIN
tblJoinContactsAndCategories ON

tblAddressListCategories.SubCategory_ID

=

tblJoinContactsAndCategories.SubCategory_ID) ON tblAddressList.ID =
tblJoinContactsAndCategories.ContactAddress_ID
WHERE (((tblAddressListCategories.Description)=[Enter Description]))
ORDER BY tblAddressList.LastName;