Selecting records

  • Thread starter Thread starter David Mitchell
  • Start date Start date
D

David Mitchell

Howdy,

I have the following problem with some data.

Site id depth "Fizzyness"

1001 42 1-slight
1001 75 1-slight
1001 100 3-High


4004 10
4004 100 1-slight
4004 200


Of the two records above I want to create a query that
returns only
those records which are "Fizzy" for all depth values. So
in the above
example I want to see site 1001 returned, and 4004 left
out.
 
David,

All you need is a subquery in the crireria, something like:
Not In (Select TableName.[Site id] From TableName Where TableName.Fizzyness
Is Null)
as the criterion on Site id; just change TableName to the actual name of
your table.

HTH,
Nikos
 
Howdy again

If you look at my original post you will see the probelm I have. I have constructed the following SQL to try and deal with it, but it returns all records. What I want it to do is only return a photo and site number if all depth values have a reaction to HCl. I do not want records where only some depth values have null values

SELECT [Soil site data 1].[Photo No and site number], [Soil site data 1].[Depth], [Soil site data 1].[Reaction to HCl
FROM [Soil site data 1
WHERE ((([Soil site data 1].[Reaction to HCl])="1- Slight" Or ([Soil site data 1].[Reaction to HCl])="2- Moderate" Or ([Soil site data 1].[Reaction to HCl])="3- Strong") NOT IN (Select [Soil site data 1].[Photo No and site number] From [Soil site data 1] Where [Soil site data 1].[Reaction to HCl] Is Null)
ORDER BY [Soil site data 1].[Photo No and site number], [Soil site data 1].[Depth]

Hope this helps

David Mitchell
 
Howdy again

If you look at my original post you will see the probelm I have. I have constructed the following SQL to try and deal with it, but it returns all records. What I want it to do is only return a photo and site number if all depth values have a reaction to HCl. I do not want records where only some depth values have null values

SELECT [Soil site data 1].[Photo No and site number], [Soil site data 1].[Depth], [Soil site data 1].[Reaction to HCl
FROM [Soil site data 1
WHERE ((([Soil site data 1].[Reaction to HCl])="1- Slight" Or ([Soil site data 1].[Reaction to HCl])="2- Moderate" Or ([Soil site data 1].[Reaction to HCl])="3- Strong") NOT IN (Select [Soil site data 1].[Photo No and site number] From [Soil site data 1] Where [Soil site data 1].[Reaction to HCl] Is Null)
ORDER BY [Soil site data 1].[Photo No and site number], [Soil site data 1].[Depth]

Hope this helps

David Mitchell
 
Fix the problem

Thanks for your help Nikos. :

This is how it looks finally

SELECT [Soil site data 1].[Photo No and site number], [Soil site data 1].Depth, [Soil site data 1].[Reaction to HCl
FROM [Soil site data 1
WHERE ((([Soil site data 1].[Photo No and site number]) Not In (Select [Soil site data 1].[Photo No and site number] From [Soil site data 1] Where [Soil site data 1].[Reaction to HCl] Is Null)) AND (([Soil site data 1].[Reaction to HCl]) Is Not Null)
ORDER BY [Soil site data 1].[Photo No and site number], [Soil site data 1].Depth

Cheers

David Mitchell
 
Back
Top