Totals Query

[Guest]

I have the following SQL
PARAMETERS [Start Date] DateTime, [End Date] DateTime
SELECT Compare2.ClNum, First(Compare2.[Client Name]) AS [FirstOfClient Name], Count(Compare2.ClNum) AS COUNTOFCLNUM, First(Compare2.EnrollDt) AS FirstOfEnrollDt, Last(IIf([EnrollDt]=[Start date],"Dropped","New")) AS Statu
FROM Compare
WHERE (((Compare2.EnrollDt) Between [Start Date] And [End Date])
GROUP BY Compare2.ClNu
HAVING (((Count(Compare2.ClNum))=1))

The part of the SQL that says "Last(IIf([EnrollDt]=[Start date],"Dropped","New")) AS Status" is not working. It works if the EnrollDt is equal to the date entered into the parameter [Start Date] but if the Start Date is greater than the EnrollDt than it doesn't work. I don't know how to construct this so the "day" doesn't matter - just the mth and year. Any help would be appreciated. Also, please be specific because I am new to Access.

 

[Ted Allen]

I think that the problem is that the last function is
outside of the iif() function, so it is only seeing the
result, which will either be "Dropped", or "New". It
looks like only the enrollment date needs to be clarified
as last/first/etc. Try using:

IIf(Last([EnrollDt])=[Start date],"Dropped","New")

and see if that will work.

Post back if it doesn't

-Ted Allen

-----Original Message-----
I have the following SQL:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT Compare2.ClNum, First(Compare2.[Client Name]) AS

[FirstOfClient Name], Count(Compare2.ClNum) AS
COUNTOFCLNUM, First(Compare2.EnrollDt) AS
FirstOfEnrollDt, Last(IIf([EnrollDt]=[Start
date],"Dropped","New")) AS Status

FROM Compare2
WHERE (((Compare2.EnrollDt) Between [Start Date] And [End Date]))
GROUP BY Compare2.ClNum
HAVING (((Count(Compare2.ClNum))=1));

The part of the SQL that says "Last(IIf([EnrollDt]=

[Start date],"Dropped","New")) AS Status" is not
working. It works if the EnrollDt is equal to the date
entered into the parameter [Start Date] but if the Start
Date is greater than the EnrollDt than it doesn't work.
I don't know how to construct this so the "day" doesn't
matter - just the mth and year. Any help would be
appreciated. Also, please be specific because I am new
to Access.

 

[John Spencer (MVP)]

First of all, you may want to use MIN and MAX to get your values - FIRST and
LAST basically return A value from a record, but not necessarily the first or
last record in the group.

Next, I think you need to stick the Max(Enroll_Dt) Inside the IIF statement and
I believe you will need Group by the Expression that you are generating.

So, based on that I end up with the following UNTESTED SQL statement

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT Compare2.ClNum,
First(Compare2.[Client Name]) AS [FirstOfClient Name],
Count(Compare2.ClNum) AS COUNTOFCLNUM,
Min(Compare2.EnrollDt) AS EarliestEnrollDt,
IIf(Max([EnrollDt])=[Start date],"Dropped","New") AS Status
FROM Compare2
WHERE (((Compare2.EnrollDt) Between [Start Date] And [End Date]))
GROUP BY Compare2.ClNum,
IIF(Max([EnrollDt])=[Start date],"Dropped","New")
HAVING (((Count(Compare2.ClNum))=1));