Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that includes the following fields

Category Name
PC John brown
Monitor Joe Blow
Portable Jane Doe
PDA Beth Oriz
Keyboard Micky Mouse

I only want to pull out data if a user (from name field) had a PC, a PDA and
a keyboard.(from category field) Can anyone tell me how.
They must have all three
 
I only want the 3 items if a user owns ALL three. If user Jane Doe only has
a pc and pda, I don't want that info. If she owns the keyboard also, then I
want it. Does that make more sense?
 
Give this a try:
SELECT *
FROM YourTable
WHERE Category in ("PC", "PDA", "Keyboard)
GROUP BY Name
HAVING Count(Name) >= 3;

-LGC
 
Your database isn't well normalized to achieve that kind of result. For
that, you need at least 3 tables:

tblPeople
PeopleID
FName
LName
---Other fields related to a person

tblEquipment
EquipmentID
Category
EquipmentDescription
---Other fields related to equipment

tblPeopleEquipment
PeopleID
EquipmentID

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
You are correct. I should have stipulated that my suggestion would work
with non-duplicate records only. Thanks.

-LGC
 
SELECT [Name]
FROM Table
WHERE Exists
(Select * FROM Table As T
WHERE T.Category = "PC" and
T.[Name] = Table.[Name)
AND Exists (Select * FROM Table As T
WHERE T.Category = "PDA" and
T.[Name] = Table.[Name)
AND Exists (Select * FROM Table As T
WHERE T.Category = "Keyboard" and
T.[Name] = Table.[Name)

You could probably do this a lot faster with a series of queries.

QueryA
SELECT Distinct [Name], [Category]
FROM Table
Where Category in ("Pc","Pda","Keyboard')

QueryB
SELECT [Name], [Category]
FROM Table
WHERE [Name] In
(SELECT [Name]
FROM QueryA
GROUP BY [Name]
HAVING Count[Name] = 3)
 
Back
Top