Unique Values in Query

[genedi]

Hi,

Building a query and have two tables joined. One table has a list of computers and the other has a list of software. I'm trying to ascertain which computers don't have a certain software installed(in this case its McAfee DLP Endpoint). I'm finding with my query I'm getting all software titles installed on a computer and it only eliminates that specific title of software. What i'd like to do is have it only show the computer that does not have that software title once.


Hopefully I've not confused anyone.....hope someone can help.

Thanks,



Gene

[June7]

Did you try using the Find Unmatched query wizard to help construct query?

[orange]

Based on your tables, I think if you use the software table to identify those computernames that do have
the software, then create another query to identify those computernames that are not in the first query.

Th sql would look like this in one query

SELECT TblComputer.computername
FROM TblComputer
WHERE computername NOT IN 
(
  SELECT tblSoft_ware.ComputerName
  FROM tblSoft_ware
  WHERE  tblSoft_ware.SoftwareName ="McAfee DLP Endpoint" 
);

However, I question your table designs generally. Using SoftwareId as the Primary Key, I would expect each Software to be uniquely identified by that PK (expectation not absolute). But if 20 computers all have software "MzToolsForVBA", your design would have 20 different SoftwareID values for the same software. But you know what you mean by SoftwareID and readers don't.

I think a more practical design would be

tblComputer
with a unique id for each computer
and only info specific to the computer

tblSoftware
with softwareId a unique number assigned to a specific software product

tblComputerHasSoftware
a junction table that identifies
the computer by ID
and the software by softwareID
and the other details related to this software on this computer
eg. installed, version, publisher... whatever makes sense in your business


Good luck.