Review date on Report for last record for each client?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I have a Main table and a Detail table. The Main Table contacts the
Client Demographics and the Detail table contains client participation
information. (Action.Field, ActionDate.Field, Status.Field, and
Status_Date.Field.

In a report I have been able to do a text field that calulates a Review
Date using =DateAdd("m",6,[maxdate]) from the maxdate text field
=Max([STATUS_DATE])

That is working fine no problem.

I want to do a query that will give me a report that shows the last
Active Case (Action Field) with the last record for each client so I
can see the Review Date without all the other records that are grouped
by individual client.

I can get the query to pull up all the Active Cases, but when I try to
do the Totals, Group By, and then use MAX I get an error code that says
I can't use the Max code.

Could someone please help me with this?
 
I can get the query to pull up all the Active Cases, but when I try to
do the Totals, Group By, and then use MAX I get an error code that says
I can't use the Max code.

Could someone please help me with this?
Click to expand...

Please open this query in SQL view and post the SQL code here. It's a
bit more than difficult to diagnose the problem without seeing the
query!

John W. Vinson[MVP]
 
SELECT CLIENTDEMOGRAPHICS.FIRST, CLIENTDEMOGRAPHICS.MI,
CLIENTDEMOGRAPHICS.LAST, CLIENTDEMOGRAPHICS.SUFX, PARTICIPATION.SSN1,
PARTICIPATION.ACTION, PARTICIPATION.ACTION_DATE, PARTICIPATION.STATUS,
Max(PARTICIPATION.STATUS_DATE) AS MaxOfSTATUS_DATE, *
FROM CLIENTDEMOGRAPHICS INNER JOIN PARTICIPATION ON
CLIENTDEMOGRAPHICS.SSN = PARTICIPATION.SSN1
GROUP BY CLIENTDEMOGRAPHICS.FIRST, CLIENTDEMOGRAPHICS.MI,
CLIENTDEMOGRAPHICS.LAST, CLIENTDEMOGRAPHICS.SUFX, PARTICIPATION.SSN1,
PARTICIPATION.ACTION, PARTICIPATION.ACTION_DATE, PARTICIPATION.STATUS
HAVING (((PARTICIPATION.STATUS)="ACTIVE"));
 
Back
Top