Calulated field to do Reviews?

[Randy]

I have a Main Table with client demographics and a Detail Table with
Action History

The Action History has the following fields

Action Field= Initial, Recert, Change
Action Date Field= Date the Action occurred
Status Field=Active, Denied, Terminated
Status Date Field=Date the Status was changed

I now need to set up a Review Date (Calculated Field) 6 months from the
Last Status Date where there was a Initial, or Recert Action completed
and then pull up a report.

I understand I need to do a Query, but how do I get it to calculate
from the Last Status Date when there could be a many previous Initial
or Recert Dates. I understand how to set up a calculated field from a
text box. It is the formula I need.

Is there a way to do this? Thanks for any help..........

[pietlinden]

Query 2:
SELECT qryIntermediateNeedFollowup.AccountID,
Max(qryIntermediateNeedFollowup.ActionDate) AS MaxOfActionDate,
DateAdd("m",6,[qryIntermediateNeedFollowup]![ActionDate]) AS NextDate
FROM qryIntermediateNeedFollowup
GROUP BY qryIntermediateNeedFollowup.AccountID,
DateAdd("m",6,[qryIntermediateNeedFollowup]![ActionDate]);

Query1 (qryIntermediateNeedFollowup):
SELECT tblActionHistory.AccountID, tblActionHistory.ActionDate,
[Action] In ("Initial","Recert") AS NeedsFollowup
FROM tblActionHistory
WHERE (((tblActionHistory.Action) In ("Initial","Recert")));
The first query just puts all the Initial/Recert records together so
that I can grab the latest value. Then the second query does it.
Maybe you can do this with one query, but you can play with that part.
Hope this gets you started.
Pieter

[Randy]

Pieter

Can you translate this to a newbie that doesn't know code?
I can do a query that will select the Initial/Recert and can set it up
to pull up the data between certain dates, but get lost after
that.........

[Alan Webb]

Randy,
Maybe, but translating a set of mouse movements and such in the Query By
Example Grid--you know this as a query window--is cumbersome. Behind the
scenes Access is translating your mouse movements & so on into SQL. What
Piet wrote for you is the end result. A lot of us have become accustomed to
typing SQL without the help of the QBE grid. I am willing to talk you
through the basics of SQL if you respond here.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"
"Randy" <ra****@msn.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Pieter

Can you translate this to a newbie that doesn't know code?
I can do a query that will select the Initial/Recert and can set it up
to pull up the data between certain dates, but get lost after
that.........

[pietlinden]

Randy,
create a new query. Don't put any tables in it. Then hit the SQL
button on the top left. (I think it's a pulldown - I'm not looking at
it). One option should be SQL. Select that. Then copy the SQL
statement above for Query1 and name it as I did. Then save it. Then
do the same for the second query. Does this get you where you need to
go?
There's no code here.... none whatsoever. It's all pure SQL

[Alan Webb]

Randy,
Sorry, I am going to follow Piet Linden's lead and try again. From your
message, it sounds like we just want to look for the last thing that
happened with a client. I'll have to make some guesses about your tables,
so forgive me if what I write won't work without changes. So, an example
select statement might look like:

"SELECT LAST_STATUS_VW.CLIENT_NAME,
DATEADD("M",6,LAST_STATUS_VW.LAST_STATUS_DATE) AS NEXT_REVIEW_DATE FROM
(SELECT TOP 1 CLIENT_TBL.FULL_NAME AS CLIENT_NAME,
ACTION_HISTORY_TBL.STATUS_DATE AS LAST_STATUS_DATE FROM CLIENT_TBL INNER
JOIN ACTION_HISTORY_TBL ON CLIENT_TBL.ID = ACTION_HISTORY_TBL.CLIENT_ID
ORDER BY STATUS_DATE DESC) AS LAST_STATUS_VW;"

This is perhaps a bit simpler than Piet's attempt. Both of us are guessing
a bit because we only have a limited knowledge of the database from which
the data is derived. SQL is actually an attempt at simplifying the task of
requesting data from a database compared to what came before it. Basic
SELECT statements are fairly straightforward. I'd encourage you to invest
in a SQL for Dummies book or some such. You get better results from the
database, you are worth more money, and wealth is sexier.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"Randy" <ra****@msn.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I have a Main Table with client demographics and a Detail Table with
Action History

The Action History has the following fields

Action Field= Initial, Recert, Change
Action Date Field= Date the Action occurred
Status Field=Active, Denied, Terminated
Status Date Field=Date the Status was changed

I now need to set up a Review Date (Calculated Field) 6 months from the
Last Status Date where there was a Initial, or Recert Action completed
and then pull up a report.

I understand I need to do a Query, but how do I get it to calculate
from the Last Status Date when there could be a many previous Initial
or Recert Dates. I understand how to set up a calculated field from a
text box. It is the formula I need.

Is there a way to do this? Thanks for any help..........

[Thelma Lubkin]

pi********@hotmail.com wrote:
<removed Query2>
: Query1 (qryIntermediateNeedFollowup):
: SELECT tblActionHistory.AccountID, tblActionHistory.ActionDate,
: [Action] In ("Initial","Recert") AS NeedsFollowup
: FROM tblActionHistory
: WHERE (((tblActionHistory.Action) In ("Initial","Recert")));
: The first query just puts all the Initial/Recert records together so
: that I can grab the latest value. Then the second query does it.
: Maybe you can do this with one query, but you can play with that part.
: Hope this gets you started.

I too am new to this environment. You [pietlinden] make a second
post in which you say that these are simply SQL statements, not
code at all. Does this mean that I cannot put a SQL statement
like Query1 above into a piece of code, e.g. a subroutine that
handles an event like click? I see this select as defining an
array NeedsFollowup and I was hoping that I could just put such
'code' into a subroutine and use it for further processing.
--thelma

: Pieter

[Alan Webb]

Randy,
Belay that, the SELECT TOP 1 will only get you the last client that had
activitiy. Ok, never mind, then.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Alan Webb" <kn*******@hotSPAMmail.com> wrote in message
news:dY********************@comcast.com...

Randy,
Sorry, I am going to follow Piet Linden's lead and try again. From your
message, it sounds like we just want to look for the last thing that
happened with a client. I'll have to make some guesses about your tables,
so forgive me if what I write won't work without changes. So, an example
select statement might look like:

"SELECT LAST_STATUS_VW.CLIENT_NAME,
DATEADD("M",6,LAST_STATUS_VW.LAST_STATUS_DATE) AS NEXT_REVIEW_DATE FROM
(SELECT TOP 1 CLIENT_TBL.FULL_NAME AS CLIENT_NAME,
ACTION_HISTORY_TBL.STATUS_DATE AS LAST_STATUS_DATE FROM CLIENT_TBL INNER
JOIN ACTION_HISTORY_TBL ON CLIENT_TBL.ID = ACTION_HISTORY_TBL.CLIENT_ID
ORDER BY STATUS_DATE DESC) AS LAST_STATUS_VW;"

This is perhaps a bit simpler than Piet's attempt. Both of us are
guessing a bit because we only have a limited knowledge of the database
from which the data is derived. SQL is actually an attempt at simplifying
the task of requesting data from a database compared to what came before
it. Basic SELECT statements are fairly straightforward. I'd encourage
you to invest in a SQL for Dummies book or some such. You get better
results from the database, you are worth more money, and wealth is sexier.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"Randy" <ra****@msn.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I have a Main Table with client demographics and a Detail Table with
Action History

The Action History has the following fields

Action Field= Initial, Recert, Change
Action Date Field= Date the Action occurred
Status Field=Active, Denied, Terminated
Status Date Field=Date the Status was changed

I now need to set up a Review Date (Calculated Field) 6 months from the
Last Status Date where there was a Initial, or Recert Action completed
and then pull up a report.

I understand I need to do a Query, but how do I get it to calculate
from the Last Status Date when there could be a many previous Initial
or Recert Dates. I understand how to set up a calculated field from a
text box. It is the formula I need.

Is there a way to do this? Thanks for any help..........

[Alan Webb]

Thelma,
SQL can be run from code. I do it all the time. It isn't as simple as
pasting the text of the SQL statement into a function in the VB editor
window, but it isn't hugely difficult either.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:d6**********@uwm.edu...

pi********@hotmail.com wrote:

Does this mean that I cannot put a SQL statement like Query1 above into a
piece of code,
e.g. a subroutine that handles an event like click?