By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Difference between Assessments

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblAssessment.AssessmentID
  2.      , tblAssessment.Client
  3.      , ([First Name] & " " & [Last Name]) AS [Client Name]
  4.      , tblClients.Program
  5.      , tblClients.[Last Name]
  6.      , tblAssessment.AssessDate
  7.      , tblAssessment.AssessmentType
  8.      , ([expliveindepend]+[LookForHousing]+[AccAffordHouse]+[HouseRelations]) AS Housing
  9.      , ([MedUnderstand]+[AdvocateWorkMeds]+[MedAdherance]+[MedRefills]+[MedDocComm]+[DocAdherence]) AS Medications
  10.      , tblAssessment.Expliveindepend
  11.      , tblAssessment.LookForHousing
  12.      , tblAssessment.AccAffordHouse
  13.      , tblAssessment.HouseRelations
  14.      , tblAssessment.MedUnderstand
  15.      , tblAssessment.AdvocateWorkMeds
  16.      , tblAssessment.MedAdherance
  17.      , tblAssessment.MedRefills
  18.      , tblAssessment.MedDocComm
  19.      , tblAssessment.DocAdherence
  20.      , tblAssessType.AssessID
  21. FROM   tblAssessType
  22.        INNER JOIN
  23.        (tblClients
  24.        INNER JOIN
  25.        tblAssessment
  26.   ON   tblClients.CID = tblAssessment.Client)
  27.   ON   tblAssessType.AssessID = tblAssessment.AssessmentType;
Mar 12 '12 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 10K+
P: 12,366
Some sample data and results would help us understand what you're looking for.
Mar 12 '12 #2

P: 4
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
Expand|Select|Wrap|Line Numbers
  1. Client     AssessNo    Housing     Medications
  2. 10122512      2           14           20
  3. 10122512      3           -8           -8
  4. 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.

Expand|Select|Wrap|Line Numbers
  1.  Client  AssessID  Program   AssessDate  AssessmentType  Housing  Medications
  2. 10122512    1     SCIL 49AH1  7/13/2011  Intake             6          8
  3. 10122512    2     SCIL 49AH1   3/1/2012  1st Assessment    20         28
  4. 10122512    3     SCIL 49AH1  12/2/2012  2nd Assessment    12         20
  5. 10185120    1     SCIL 49AH1   7/3/2012  Intake             7         13
  6. 10185552    1     BLVD 49AH1   3/2/2012  Intake            12          7
  7. 10185552    2     BLVD 49AH1   5/3/2012  1st Assessment     7         16
My new SQL is:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAssessment.AssessmentID
  2.      , tblAssessment.Client
  3.      , tblClients.Program
  4.      , tblClients.[Last Name]
  5.      , tblAssessment.AssessDate
  6.      , tblAssessment.AssessmentType
  7.      , ([expliveindepend]+[LookForHousing]+[AccAffordHouse]+[HouseRelations]) AS Housing
  8.      , ([MedUnderstand]+[AdvocateWorkMeds]+[MedAdherance]+[MedRefills]+[MedDocComm]+[DocAdherence]) AS Medications
  9.      , tblAssessType.AssessID
  10. FROM   tblAssessType
  11.        INNER JOIN
  12.        (tblClients
  13.        INNER JOIN
  14.        tblAssessment
  15.   ON   tblClients.CID = tblAssessment.Client)
  16.   ON   tblAssessType.AssessID = tblAssessment.AssessmentType;
Thanks for trying to help,
Mar 12 '12 #3

Expert Mod 15k+
P: 31,494
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.
Mar 12 '12 #4

Expert Mod 10K+
P: 12,366
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.
Mar 13 '12 #5

P: 4
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.
Mar 14 '12 #6

Expert Mod 10K+
P: 12,366
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.
Mar 14 '12 #7

P: 4
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?
Mar 14 '12 #8

Expert Mod 15k+
P: 31,494
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.
Mar 14 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.