Iif Statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Hi Ryan,

You can do what you say or just simply just sort them on the TypeOfCall field.
 
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.
 
Back
Top