I am working on a report that will be used to highlight gaps in recording data, namely assessments.
The first part of my report returns the latest (max) Review Assessment for a client. I then need to requery the same table to find out when the follow up assessment took place and provide the difeerence in days betrween the review and follow up assessment.
the data I need to return will look somethinhg like this
Client No
1234
There will be instances where the follow up assesment has not yet taken place.
All the assessments are stored in the same table. what I have done is to select the same table twice in Database Expert and aliased it, the first assessment table returns the Review Assessment date, this seems to be OK. When I query the table again to return the follow up assessment date, it is returning the review assessment date.
I suspect its to do with the selection criteria i have placed in Group Selection criteria to return the latest assessment date, this statement looks like this:
{ASSESSMENT.Assessment_Date} = maximum({ASSESSMENT.Assessment_Date},CLIENT.ClientNo})
I have grouped on ClientNo and moved all my fields to the group header.
The report is made up of the following tables CLIENT, ASSESSMENT and ASSESSMENT_Q_REVIEW and ASSESSMENT_Q_FOLLOWUP.
I have ASSESSMENT twice, aliased as ASSESSMENT_REVIEW and ASSESSMENT_FUP
I have joined up the tables in the followiong manner:
CLIENT to ASSESSMENT_REVIEW to ASSESSMENT_Q_REVIEW - I have to link it to the Q_REVIEW table so that only review assessments are selected. These tables return the lastest assessment
And then CLIENT to ASSESSMENT_FUP to ASSESSMENT_Q_FOLLOWUP to retutn follow up assessments.
Any ideas, feedback greatly appreciated!
The first part of my report returns the latest (max) Review Assessment for a client. I then need to requery the same table to find out when the follow up assessment took place and provide the difeerence in days betrween the review and follow up assessment.
the data I need to return will look somethinhg like this
Client No
Name
Review Assessment Date
Follow up Assessment date
number of days between1234
Joe Bloggs
01/02/2014
18/02/2014
17There will be instances where the follow up assesment has not yet taken place.
All the assessments are stored in the same table. what I have done is to select the same table twice in Database Expert and aliased it, the first assessment table returns the Review Assessment date, this seems to be OK. When I query the table again to return the follow up assessment date, it is returning the review assessment date.
I suspect its to do with the selection criteria i have placed in Group Selection criteria to return the latest assessment date, this statement looks like this:
{ASSESSMENT.Assessment_Date} = maximum({ASSESSMENT.Assessment_Date},CLIENT.ClientNo})
I have grouped on ClientNo and moved all my fields to the group header.
The report is made up of the following tables CLIENT, ASSESSMENT and ASSESSMENT_Q_REVIEW and ASSESSMENT_Q_FOLLOWUP.
I have ASSESSMENT twice, aliased as ASSESSMENT_REVIEW and ASSESSMENT_FUP
I have joined up the tables in the followiong manner:
CLIENT to ASSESSMENT_REVIEW to ASSESSMENT_Q_REVIEW - I have to link it to the Q_REVIEW table so that only review assessments are selected. These tables return the lastest assessment
And then CLIENT to ASSESSMENT_FUP to ASSESSMENT_Q_FOLLOWUP to retutn follow up assessments.
Any ideas, feedback greatly appreciated!