Query Using Multiple Categories

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I have a database of names that each name can belong to one or more
categories.
I have 3 tables - Names, Category, Type.

Names - Client ID, Name, Address
Category - Category ID, Client ID, Type
Type - List of Categories - A, B, C, D, E

I have a form for each name which includes a subform to select one or many
categories for each record.

1. How do I perform a query to show all records belonging to BOTH category
A and C only.

2. How can I perform a query to show all records belonging to more than one
category.

Thanks
Craig
 
Dear Craig:

How do tables Category and Type relate. You have a Category ID column
in Category. Does this relate to the categories in table Type? But
you also have a column Type in table Category? Is this how they
relate?

I have a considerable answer worked out based on some assumptions that
may be faulty. Please give this and details of table Type so I can
try to finish this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Category uses a lookup to the Type
CategoryID in category is not related and really shouldnt be there. I have
deleted this

The Category Table is the relationship between the NameTable and TypeTable :

ClientID - Type
1 A
1 B
2 A
2 C
3 A
3 B
3 D

Thanks for looking at this

Craig
 
Dear Craig:

The specific responses are inline below.

I have not assumed that [Category ID] / [Client ID] is unique in table
Category. If it is, things could be simplified.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a database of names that each name can belong to one or more
categories.
I have 3 tables - Names, Category, Type.

Names - Client ID, Name, Address
Category - Category ID, Client ID, Type
Type - List of Categories - A, B, C, D, E

I have a form for each name which includes a subform to select one or many
categories for each record.

1. How do I perform a query to show all records belonging to BOTH category
A and C only.
Click to expand...

SELECT [Client ID]
FROM Category C
WHERE EXISTS (SELECT * FROM Category C1
WHERE C1.[Client ID] = C.[Client ID] AND C1.Type = 'A')
AND EXISTS (SELECT * FROM Category C1
WHERE C1.[Client ID] = C.[Client ID] AND C1.Type = 'C')
AND NOT EXISTS (SELECT * FROM Category C1
WHERE C1.[Client ID] = C.[Client ID]
AND C1.Type NOT IN ( 'A', 'C'))

From this you can join to other tables to retrieve any other columns
needed given that you have the row from Category available.
2. How can I perform a query to show all records belonging to more than one
category.
Click to expand...

SELECT [Category ID]
FROM Category C
WHERE (SELECT COUNT(*) FROM
(SELECT DISTINCT [Category ID], Type FROM Category C) X)
WHERE X.[Category ID] = C.[Category ID]) > 1
 
Table Category values are unique
The primary Key consists of both fields ClientID and Category

Craig

Tom Ellison said:
Dear Craig:

The specific responses are inline below.

I have not assumed that [Category ID] / [Client ID] is unique in table
Category. If it is, things could be simplified.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have a database of names that each name can belong to one or more
categories.
I have 3 tables - Names, Category, Type.

Names - Client ID, Name, Address
Category - Category ID, Client ID, Type
Type - List of Categories - A, B, C, D, E

I have a form for each name which includes a subform to select one or many
categories for each record.

1. How do I perform a query to show all records belonging to BOTH category
A and C only.
Click to expand...

SELECT [Client ID]
FROM Category C
WHERE EXISTS (SELECT * FROM Category C1
WHERE C1.[Client ID] = C.[Client ID] AND C1.Type = 'A')
AND EXISTS (SELECT * FROM Category C1
WHERE C1.[Client ID] = C.[Client ID] AND C1.Type = 'C')
AND NOT EXISTS (SELECT * FROM Category C1
WHERE C1.[Client ID] = C.[Client ID]
AND C1.Type NOT IN ( 'A', 'C'))

From this you can join to other tables to retrieve any other columns
needed given that you have the row from Category available.
2. How can I perform a query to show all records belonging to more than one
category.
Click to expand...

SELECT [Category ID]
FROM Category C
WHERE (SELECT COUNT(*) FROM
(SELECT DISTINCT [Category ID], Type FROM Category C) X)
WHERE X.[Category ID] = C.[Category ID]) > 1
Thanks
Craig
Click to expand...
Click to expand...
 
Back
Top