Percent Change Limited to a Unique ID (1 Viewer)

MuleBruiser63

New member
Local time
Today, 10:58
Joined
Feb 16, 2018
Messages
2
Hi all,

I am a beginner with Access 2010 and this is my first post. I have searched extensively for weeks on this forum and elsewhere and have not found a solution to my problem. I have figured out how to run all of my queries/calculations except for one thus far in my research database. I would like to be able to calculate the percent change between fields for a Unique ID but not have the calculation run between next set of fields when moving onto the next Unique ID. The Medical Record Number (MRN) is the unique ID that I would like to run my query on. It would be simple if I did not have that much data as I could pull out each MRN and run a query, however I currently have > 1500 scores for > 200 MRNs so I was hoping to pick everyone's brain to see if they have a solution, or if I need to figure this out in Excel or just breakdown and get a separate statistical software package. Any help is much appreciated. Thanks!

My table is set up as such MRN, VisitDate, Score.

Sample Data Is:
MRN VisitData Score
1 7/1/2016 9
1 8/1/2016 6
1 9/1/2016 3
2 2/1/2017 6
2 3/1/2017 0

And I would like the query to show results as such:
MRN PercentChange
1
1 -33%
1 -50%
2
2 -100%
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 10:58
Joined
Apr 27, 2015
Messages
6,281
Welcome to AWF MuleBruiser63,

In what way do you want to display this info? On a form or report? Will it always be a running equation based on the MRN or do you plan on adding a date-range function?
 

JHB

Have been here a while
Local time
Today, 15:58
Joined
Jun 17, 2012
Messages
7,732
Run the query in the attached database.
 

Attachments

  • DBPercentChange.accdb
    380 KB · Views: 55

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:58
Joined
May 7, 2009
Messages
19,169
SELECT tblMRN.MRN, tblMRN.Score, ([score]-(select top 1 t1.[score] from tblmrn as t1 where t1.[mrn]=tblmrn.mrn and t1.visitdate < tblmrn.visitdate order by t1.mrn asc, t1.visitdate desc))/(select top 1 t1.[score] from tblmrn as t1 where t1.[mrn]=tblmrn.mrn and t1.visitdate < tblmrn.visitdate order by t1.mrn asc, t1.visitdate desc) AS PercentChange
FROM tblMRN
ORDER BY tblMRN.MRN, tblMRN.VisitDate;


'''''
On design view of the query format PercentChange As Percent.
 

MuleBruiser63

New member
Local time
Today, 10:58
Joined
Feb 16, 2018
Messages
2
Everyone thanks so much for your help! I finally solved my weeks long conundrum with everyone's guidance.

pbaldy I took the link you provided and matched it up with the formula provided by arnelgp. I got some errors with arnelgp's formula but it just may be my version of access/how I set up my tables.

JHB thanks for the table. I did not get to download it as my work computer prevents downloads.

NauticalGent thanks for the reply. The score is based on a patient's recovery from a particular disease. The goal is to get the percent change per visit for each patient, then average the percent change for each patient, and average the overall percent change from all the data. It will be reported as the overall average percent change in the final report. This is the simplified version of what eventually will be done, as the scores will be compared to patient age, gender, etc.



In case someone else needs something similar to me in the future, below are my final SQL statements for my table named Visit with fields of MRN, VisitDate, and Score:



Percent Change Only Formula:


SELECT Visit.MRN, Visit.VisitDate, Visit.Score, ((Visit.Score-(SELECT TOP 1 t1.Score
FROM Visit AS t1
WHERE t1.MRN = Visit.MRN
AND t1.VisitDate < Visit.VisitDate
ORDER BY t1.VisitDate desc, t1.MRN))/(SELECT TOP 1 t1.Score FROM Visit AS t1 WHERE t1.MRN = Visit.MRN AND t1.VisitDate < Visit.VisitDate ORDER BY t1.VisitDate desc, t1.MRN)) AS PercentChange
FROM Visit;




Since some of my scores would result in #/0, I got the #Num! error and corrected the above formula as such:


SELECT Visit.MRN, Visit.VisitDate, Visit.Score,
IIf((SELECT TOP 1 t1.Score
FROM Visit AS t1
WHERE t1.MRN = Visit.MRN
AND t1.VisitDate < Visit.VisitDate
ORDER BY t1.VisitDate desc, t1.MRN) = 0, 0,
(Visit.Score-(SELECT TOP 1 t1.Score FROM Visit AS t1 WHERE t1.MRN = Visit.MRN AND t1.VisitDate < Visit.VisitDate ORDER BY t1.VisitDate desc, t1.MRN))/(SELECT TOP 1 t1.Score
FROM Visit AS t1
WHERE t1.MRN = Visit.MRN
AND t1.VisitDate < Visit.VisitDate
ORDER BY t1.VisitDate desc, t1.MRN)) AS PercentChange FROM Visit;
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 10:58
Joined
Apr 27, 2015
Messages
6,281
Glad you got it sorted. No surprise really, you had some of the best AWF has at its disposal assisting!
 

Users who are viewing this thread

Top Bottom