Separating field information

[Marv]

I need some help. Can this be done with queries?

Database has 13,000 records


All records are identical except for the History Field that will contain one
number 1, 2, 3, or 4


In a query that contains no records with a history of 2 select the records
with a history of 1 but not 3 or 4.

In a query that contains no records with a history of 1 select the records
with a history of 2 but not 3 or 4.

Thanks for your help.

Marv Trott

 

[John Spencer]

That is unclear to me. You must be leaving out some necessary detail,
since the way I read your post all you need to do is apply criteria of
=1 for the first solution and =2 for the second solution.

Field: History
Criteria: =1

Field: History
Criteria: = 2



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marv]

John,

You are correct I left something out in my description.

Here are the important fields from eight typical records in a query

#1 Case # 234 History 1
#2 Case # 234 History 3
#3 Case # 234 History 4

#4 Case # 235 History 1

#5 Case # 236 History 1

#6 Case # 236 History 3

#7 Case # 237 History 1
#8 Case # 237 History 4

List should only display Case #235

Is this the logical way to describe this action?. If History =1 and (not = 3
or not = 4) then print

Thanks for your help.

Marv

 

[John Spencer]

One method would be to use a subquery in the where clause.

SELECT T.*
FROM YourTable As T
WHERE T.History = 1
AND NOT EXISTS
(SELECT *
FROM YourTable as T2
WHERE T2.Case = T.Case
AND T2.History in (2,3,4))


No exists is pretty slow with large sets of records. So try a two-query
approach.

Query One: Get all the records that do have a history of 2,3, or 4
SELECT Case FROM YourTable Where History in (2,3,4)

Query Two: Create an unmatched query (with the wizard) based on your
table and Query one.

SELECT T.*
FROM YourTable as T LEFT JOIN QueryOne
ON T.Case = QueryOne.Case
WHERE QueryOne.Case is Null


'====================================================

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marv]

John,

Thanks for the solution and also getting me to learn a little more about
Access.

Regards,

Marv