query for missing value

  • Thread starter Thread starter Bryce
  • Start date Start date
B

Bryce

hi,

i am using access 2003 and have a .csv that i imported into a table.
i need to extract a list of computer names that do NOT have our custom
service installed.
table looks like this:

headings are:
servername service state startuptype

data is (a brief sample):
pc1 mdm stopped manual
pc1 messenger stopped manual
pc1 pl running automatic
pc2 ep running automatic
pc2 mdm stopped manual
pc2 messenger stopped manual
pc2 netlogon running manual
pc3 mdm stopped manual
pc3 messenger stopped manual
pc3 pl running automatic
pc3 ep running automatic
pc3 netlogon running manual

i need to run a query that tells me, in an easy order, which pc's do NOT
have the service pl and/or ep
i have been using the create query with wizard option and need assistance.

Thanks.
 
Use two queries unless you know subqueries --
qryServiceCK --
SELECT servername, service
FROM YourTable
WHERE [service] = "pl" OR [service] = "ep";

SELECT servername, service, state, startuptype
FROM YourTable LEFT JOIN qryServiceCK ON YourTable.servername =
qryServiceCK.servername
WHERE qryServiceCK.servername Is Null;
 
Hi,

thanks for the help and the fast response.

i created two queries - one named "qryServiceCK" and the other named "query2"
the qryServiceCK runs fine.
when i run the query2 i get an access message:

the specified field 'servername' could refer to more than one table listed
in the FROM clause of your SQL statement.

am i running them correctly. create the query, save it, then double click it.
i have removed all the other columns (only the 4 specified and the ID column)

Thanks.


KARL DEWEY said:
Use two queries unless you know subqueries --
qryServiceCK --
SELECT servername, service
FROM YourTable
WHERE [service] = "pl" OR [service] = "ep";

SELECT servername, service, state, startuptype
FROM YourTable LEFT JOIN qryServiceCK ON YourTable.servername =
qryServiceCK.servername
WHERE qryServiceCK.servername Is Null;

--
Build a little, test a little.


Bryce said:
hi,

i am using access 2003 and have a .csv that i imported into a table.
i need to extract a list of computer names that do NOT have our custom
service installed.
table looks like this:

headings are:
servername service state startuptype

data is (a brief sample):
pc1 mdm stopped manual
pc1 messenger stopped manual
pc1 pl running automatic
pc2 ep running automatic
pc2 mdm stopped manual
pc2 messenger stopped manual
pc2 netlogon running manual
pc3 mdm stopped manual
pc3 messenger stopped manual
pc3 pl running automatic
pc3 ep running automatic
pc3 netlogon running manual

i need to run a query that tells me, in an easy order, which pc's do NOT
have the service pl and/or ep
i have been using the create query with wizard option and need assistance.

Thanks.
Click to expand...
Click to expand...
 
Add table name like this ---
SELECT YourTable.servername, YourTable.service, YourTable.state,
YourTable.startuptype
FROM YourTable LEFT JOIN qryServiceCK ON YourTable.servername =
qryServiceCK.servername
WHERE qryServiceCK.servername Is Null;

--
Build a little, test a little.


Bryce said:
Hi,

thanks for the help and the fast response.

i created two queries - one named "qryServiceCK" and the other named "query2"
the qryServiceCK runs fine.
when i run the query2 i get an access message:

the specified field 'servername' could refer to more than one table listed
in the FROM clause of your SQL statement.

am i running them correctly. create the query, save it, then double click it.
i have removed all the other columns (only the 4 specified and the ID column)

Thanks.


KARL DEWEY said:
Use two queries unless you know subqueries --
qryServiceCK --
SELECT servername, service
FROM YourTable
WHERE [service] = "pl" OR [service] = "ep";

SELECT servername, service, state, startuptype
FROM YourTable LEFT JOIN qryServiceCK ON YourTable.servername =
qryServiceCK.servername
WHERE qryServiceCK.servername Is Null;

--
Build a little, test a little.


Bryce said:
hi,

i am using access 2003 and have a .csv that i imported into a table.
i need to extract a list of computer names that do NOT have our custom
service installed.
table looks like this:

headings are:
servername service state startuptype

data is (a brief sample):
pc1 mdm stopped manual
pc1 messenger stopped manual
pc1 pl running automatic
pc2 ep running automatic
pc2 mdm stopped manual
pc2 messenger stopped manual
pc2 netlogon running manual
pc3 mdm stopped manual
pc3 messenger stopped manual
pc3 pl running automatic
pc3 ep running automatic
pc3 netlogon running manual

i need to run a query that tells me, in an easy order, which pc's do NOT
have the service pl and/or ep
i have been using the create query with wizard option and need assistance.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top