Show only unique records in duplicated field

M

Matthew

Hello,

I need to limit repeated (but not duplicated) records in a query. Here's my
structure:

tblCompanies
*CompanyID
CompanyName

tblCategories
*CategoryID
CategoryName

tblCategoryAssignment
*CatAssignmentID
CompanyID
CategoryID

This structure allows a company to be assigned more than one category.

I want to be able run a query searching for companies that belong several
categories, i.e. show me all companies that have category assignments of
either software or nonprofit. Something like TechSoup would come up twice.
I'm interested in viewing the company names, not necessarily their
CategoryAssignmentIDs. Those ID's will be unique, but company names will be
duplicated. How do I get the query results to show only uniqe company
names?

Many thanks!!

Matthew
 
S

SteveM

A Totals query - click View/Totals
Company Name should be 'Group By', AssignmentID's should be 'Where'...

Steve
 
J

John W. Vinson

I want to be able run a query searching for companies that belong several
categories, i.e. show me all companies that have category assignments of
either software or nonprofit. Something like TechSoup would come up twice.
I'm interested in viewing the company names, not necessarily their
CategoryAssignmentIDs. Those ID's will be unique, but company names will be
duplicated. How do I get the query results to show only uniqe company
names?

A couple of ways: either uncheck the Show checkbox in the query grid under the
category fields (or, equivalently, don't include the category field in the
SELECT clause of the query, only in the WHERE clause); or, use a Subquery:

SELECT tblCompanies.CompanyName
FROM tblCompanies
WHERE CompanyID In
(SELECT CompanyID FROM tblCategoryAssignment INNER JOIN tblCategories
ON tblCategoryAssignment.CategoryID = tblCategories.CategoryID
WHERE tblCategories.CategoryName = [Enter category:])

John W. Vinson [MVP]
 
M

Matthew

Hmm, I'm intrigued.

If I uncheck the Show checkbox in the category fields, it still shows
company names twice.

But the Subquery works!! Subqueries are new to me!!

Thank you!!

Matthew



John W. Vinson said:
I want to be able run a query searching for companies that belong several
categories, i.e. show me all companies that have category assignments of
either software or nonprofit. Something like TechSoup would come up
twice.
I'm interested in viewing the company names, not necessarily their
CategoryAssignmentIDs. Those ID's will be unique, but company names will
be
duplicated. How do I get the query results to show only uniqe company
names?

A couple of ways: either uncheck the Show checkbox in the query grid under
the
category fields (or, equivalently, don't include the category field in the
SELECT clause of the query, only in the WHERE clause); or, use a
Subquery:

SELECT tblCompanies.CompanyName
FROM tblCompanies
WHERE CompanyID In
(SELECT CompanyID FROM tblCategoryAssignment INNER JOIN tblCategories
ON tblCategoryAssignment.CategoryID = tblCategories.CategoryID
WHERE tblCategories.CategoryName = [Enter category:])

John W. Vinson [MVP]
 
J

John W. Vinson

Hmm, I'm intrigued.

If I uncheck the Show checkbox in the category fields, it still shows
company names twice.

sorry... should have added that you *also* need to set the Query's Unique
Values property to Yes.
But the Subquery works!! Subqueries are new to me!!

And pretty useful in many contexts. They're not always ideally efficient
though, the query optimizer can't always handle them well.

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top