Difference between Assessments

[Kelly Bolton]

Hi, I am trying to set up some reports or queries that show the difference between assessment scores. We are trying to set up queries and reports that show difference between scores if certain citeria are met, Eg between a data range, or for certain Programs. Each assessment has an initial assessment (AssessmentID=1 ) and then reassessments AssessmentID 2 or 3 or 4 etc). I guess what I am really looking for is a way to write a query where I can say show me the difference between AssessmnetID=1 and AssessmentID=2 and then between Assessment ID=2 and Assessment ID=3 and so on. I am wanting to use this to show scores that increase and decrease. I hope you can help. Below is the current SQL for the query I am using:

SELECT tblAssessment.AssessmentID
     , tblAssessment.Client
     , ([First Name] & " " & [Last Name]) AS [Client Name]
     , tblClients.Program
     , tblClients.[Last Name]
     , tblAssessment.AssessDate
     , tblAssessment.AssessmentType
     , ([expliveindepend]+[LookForHousing]+[AccAffordHouse]+[HouseRelations]) AS Housing
     , ([MedUnderstand]+[AdvocateWorkMeds]+[MedAdherance]+[MedRefills]+[MedDocComm]+[DocAdherence]) AS Medications
     , tblAssessment.Expliveindepend
     , tblAssessment.LookForHousing
     , tblAssessment.AccAffordHouse
     , tblAssessment.HouseRelations
     , tblAssessment.MedUnderstand
     , tblAssessment.AdvocateWorkMeds
     , tblAssessment.MedAdherance
     , tblAssessment.MedRefills
     , tblAssessment.MedDocComm
     , tblAssessment.DocAdherence
     , tblAssessType.AssessID
FROM   tblAssessType
       INNER JOIN
       (tblClients
       INNER JOIN
       tblAssessment
  ON   tblClients.CID = tblAssessment.Client)
  ON   tblAssessType.AssessID = tblAssessment.AssessmentType;

[Rabbit]

Some sample data and results would help us understand what you're looking for.

[Kelly Bolton]

Here is some sample data. I took all the other assemssment scores out and only left 2 (housing and Medications) which are sub totals of other scores. What I am hoping to be able to see is differnces of scores for each client. Assessment 1 is the original score, so no differnce there, but I want to see if score increased or decreased on each additional assessment. Results I want to see are:
EG Client

Client     AssessNo    Housing     Medications
10122512      2           14           20
10122512      3           -8           -8
10185552      2           -5            9

Here are my current results of my query. I took the other scores out I had in there previously to avoid more confusion.

 Client  AssessID  Program   AssessDate  AssessmentType  Housing  Medications
10122512    1     SCIL 49AH1  7/13/2011  Intake             6          8
10122512    2     SCIL 49AH1   3/1/2012  1st Assessment    20         28
10122512    3     SCIL 49AH1  12/2/2012  2nd Assessment    12         20
10185120    1     SCIL 49AH1   7/3/2012  Intake             7         13
10185552    1     BLVD 49AH1   3/2/2012  Intake            12          7
10185552    2     BLVD 49AH1   5/3/2012  1st Assessment     7         16

My new SQL is:

SELECT tblAssessment.AssessmentID
     , tblAssessment.Client
     , tblClients.Program
     , tblClients.[Last Name]
     , tblAssessment.AssessDate
     , tblAssessment.AssessmentType
     , ([expliveindepend]+[LookForHousing]+[AccAffordHouse]+[HouseRelations]) AS Housing
     , ([MedUnderstand]+[AdvocateWorkMeds]+[MedAdherance]+[MedRefills]+[MedDocComm]+[DocAdherence]) AS Medications
     , tblAssessType.AssessID
FROM   tblAssessType
       INNER JOIN
       (tblClients
       INNER JOIN
       tblAssessment
  ON   tblClients.CID = tblAssessment.Client)
  ON   tblAssessType.AssessID = tblAssessment.AssessmentType;

Thanks for trying to help,
Kelly

[NeoPa]

Kelly, please check out Before Posting (VBA or SQL) Code. It is perfectly possible to format your question in a way that won't frighten off most experts ;-) Ultimately it's you who benefits most from this.

[Rabbit]

I don't need to know the results of your current query because I assume it's wrong, otherwise you wouldn't be here. I need to know the results that you want.

[Kelly Bolton]

Im sorry, I was trying to show you the sample data I was working with. The first set of data shows you the results that I want. I am new to this site, please let me know if there is anything else you need.

[Rabbit]

Ok, so the first set of data are the results you want. The second set of data are the results you're getting. My mistake, I misread that. However, I still need to see the sample data.

[Kelly Bolton]

Maybe it is easier to say, that I want the query or report) to say when client number is the same show me the difference in housing scores between AsssessNo 2 and 3, or 3 and 4 etc. then I want to do the same for Medication Scores. Does that make sense?

[NeoPa]

Kelly:
Does that make sense?

Not really Kelly. If you have something else to offer that you believe will make it easier to understand your problem then include it by all means, but if you've been asked to provide something then it makes sense to provide that something, regardless of whatever else you may include. You're never going to be more qualified to determine what someone wants from you than they are themselves.

I may be accused of being master of the blindingly obvious here, but you really are better off co-operating with people helping you than trying to lead them to do things your way.