Iif Statements

[Guest]

I have a table that I am trying to capture "Follow Up" phone calls. I have
two fields that are Date and TypeOfCall. TypeOfCall has a value list of "1st
call", "2nd call", etc.

A patient may have only "1st call" associated with a date in the table, or
they may have multiple dates where then you would see "1st call", "2nd call",
"3rd call".

I assume that I will be writing an Iif statement. What would it look like if
I wanted to see everyone that has only 1st calls, everyone that has had a
first and second call, or etc.

Or would it be better for me to do seperate queries then do a union of them?
(I may have just answered my own question) Any advice would be appreciated.

Thank you so very much, Ryan

 

[Guest]

Hi Ryan,

You can do what you say or just simply just sort them on the TypeOfCall field.

 

[John Spencer (MVP)]

Assuming that each type of call will exist only once and that the types of calls
are always going to occur in sequence you can use a subquery to get the records.

SELECT Table.*
FROM Table
WHERE PrimaryKeyField in
(SELECT tmp.PrimaryKeyField
FROM Table as Tmp
GROUP BY Tmp.PrimaryKeyField
HAVING Count(TypeOfCall) = 1)

Change the number from 1 to 2 to 3 to ... to get an increasing number of calls.

This query is basically the query that the Find Duplicates wizard will build.
That query just says show where Count(SomeField) > 1.