I can't wrap my head around this query

[Kevin]

I have two tables. One that has ComputerID and some other fields. The
ComputerID field is unique. I also have another table that has a non-unique
Computer ID field and a SWName record for each software program installed
on that computer. What I would like to do is find out what ComputerIDs DO
NOT have a specific program (ProgramA). I was thinking I would get what I
needed if I could take the results for those ComputerIDs that DO have
ProgramA and compare that with all of the ComputerIDs possible and take the
difference. Is there some way to do this? If not, what would I have to add
to one, the other, or both tables to get what I need?

Thank you.

Kevin

 

[Jason Lepack]

This will give you all the computers that have that software: (save as
queryA)
select
ComputerID
from
your_table_with_sw
where
SWName = [Enter a prgram name]

This query will select all computers that don't exist in queryA
select
ci.*
from
your_table_with_computer_info AS ci
left join queryA as qa on qa.ComputerID = ci.ComputerID
where
qa.ComputerID is null

Cheers,
Jason Lepack

 

[Jeff Boyce]

Kevin

The query wizard includes an "Unmatched" query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kevin]

Jason,

You rock! That worked like a charm. Now, where can I learn how to do
that? Most tutorials only cover the basics...

Thanks!!!

Kevin

This will give you all the computers that have that software: (save as
queryA)
select
ComputerID
from
your_table_with_sw
where
SWName = [Enter a prgram name]

This query will select all computers that don't exist in queryA
select
ci.*
from
your_table_with_computer_info AS ci
left join queryA as qa on qa.ComputerID = ci.ComputerID
where
qa.ComputerID is null

Cheers,
Jason Lepack

I have two tables. One that has ComputerID and some other fields. The
ComputerID field is unique. I also have another table that has a non-unique
Computer ID field and a SWName record for each software program installed
on that computer. What I would like to do is find out what ComputerIDs DO
NOT have a specific program (ProgramA). I was thinking I would get what I
needed if I could take the results for those ComputerIDs that DO have
ProgramA and compare that with all of the ComputerIDs possible and take the
difference. Is there some way to do this? If not, what would I have to add
to one, the other, or both tables to get what I need?

Thank you.

Kevin

 

[Jason Lepack]

Almost everything that I know was learned through searching Google
Groups... If I couldn't find it, then I posted it in one of the
groups.

I read these ones regularly:
Microsoft.Public.Access.Queries
Comp.Databases.MsAccess
Microsoft.Public.Access
Microsoft.Public.Access.Tablesdbdesign

Cheers,
Jason Lepack

Jason,

You rock! That worked like a charm. Now, where can I learn how to do
that? Most tutorials only cover the basics...

Thanks!!!

Kevin

This will give you all the computers that have that software: (save as
queryA)
select
ComputerID
from
your_table_with_sw
where
SWName = [Enter a prgram name]

This query will select all computers that don't exist in queryA
select
ci.*
from
your_table_with_computer_info AS ci
left join queryA as qa on qa.ComputerID = ci.ComputerID
where
qa.ComputerID is null

Cheers,
Jason Lepack

- Show quoted text -