Access Query: Problem producing correct results 1

[pita2]

Hi,
I have the follwing tables:
Equipment Table:
EquipmentID(PK) EquipmentName UserName
1 IT57 Smith
2 IT54 Jack
etc

SoftwareInventory Table:
SoftwareID(PK] SoftwareVerison Type
1 MircosoftWord Base
2 CrystalReport App
etc

SoftwareInstallationDetail Table:
EquipmentID(Key) SoftwareID(Key) SoftwareVersion InstalledDate
1 1 MicrosoftWord 1/17/2005
1 2 CrystalReport 2/00/2004
etc.

User Table:
UserName(PK) UserID
Smith abc
Jack xyz
etc.


I would like to have a query that gives me all the Softwares that are NOT installed on a person's equipment. I have tried the left join, the not exists, the Subquery. It sounds very simple, but nothing works. Please help!!!! Thanks much.

 

[PHV]

A starting point:
SELECT E.*, S.*
FROM Equipment E, SoftwareInventory S
WHERE Not Exists (SELECT * FROM SoftwareInstallationDetail I
WHERE I.EquipmentID=E.EquipmentID AND I.SoftwareID=S.SoftwareID)

Another way:
SELECT A.*
FROM (SELECT E.*, S.* FROM Equipment E, SoftwareInventory S) A
LEFT JOIN SoftwareInstallationDetail I
ON A.EquipmentID=I.EquipmentID AND A.SoftwareID=I.SoftwareID
WHERE I.EquipmentID Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pita2]

Thank you So very much!! Your queries work perfectly. You are a star!!