Iif Statements

[Ryan W]

I have a table has 3 fields: name, typeofcall, date, and medicalrecord. Name
is text, typeofcall has a value list of "1,2,3,4,5" and is a text field, date
is date field and medicalrecord is my PK

The user is going to be tracking the dates that they make phone calls to the
patients. As the user makes calls, I would like to develope a report that
would tell the user what typeofcall the patient is on. For ex:

Table:
Name TypeOfCall Date
Bob 1 1/1/2002
Bob 2 2/1/2002
Bob 3 3/1/2002
Ryan 1 1/20/2002
Ryan 2 2/20/2002

I would like my report to come out like this:
Name typeofcall
Bob 3
Ryan 2

Is this an Iif Statement? Your help would be very much appreciated!

Ryan

 

[Dale Fye]

Ryan,

First lets start with your table design. [Name] and [Date] are Reserved
words. It is highly recommended NOT to give fields within your database
these names. Instead use Caller_Name and Call_Date. You imply by example
that your date field just contains dates. What happens if someone makes two
calls to the same client on the same day? I would make these Date/Time
values and use the NOW() function to fill this field. Why would you create a
text field and then put a numeric value in it? Change the name of your
TypeOfCall field to TypeOfCall_ID and make it an integer. Then create a
TypeOfCall table (TypeOfCall_ID, TypeOfCall_Desc).

If you want to get the the most information for the most recent call placed
by a each of your callers to a particular client (medical record), then you
need to start out with a query that looks like

qry_Most_Recent_Client_Caller:
SELECT Caller_Name, MAX(Call_Date) as RecentCall
FROM yourTable
WHERE MedicalRecord = "XYZ"

Then create another query that uses this first one to filter only the
information you want.

qry_SomeName:
SELECT T.*
FROM yourTable T
INNER JOIN qry_Most_Recent_Client_Caller Q
ON T.Caller_Name = Q.Caller_Name
AND T.Call_Date = Q.Call_Date

HTH
Dale