I need help to create a so-called Rosendaal calculator of INR (a measurement in blood) for our research project. Our data has 3000000 rows and here I attached a sample of it in MS Access. PT_ID is a patient identifier. INR is the test value. Date – date of the test. Each patient had multiple measurements – and is suppose to have the test value in range from 2-3 (inclusive). Our goal is to calculate the percentage of time each patient was within the specified range (2-3).
Example: Patient has an INR reading of 2.4 on October 1st, then a reading of 3.2 on October 17th. Assuming the patient gradually moves toward a reading of 3.2 throuhout the 16 day period between Oct. 1st and the 17th we can estimate that the patient was within their therapeutic range (2-3) for a majority of that time period. The spreadsheet would need to 1. calculate the total shift (example 2.4-3.2 = 0.8 increase) that is within therapeutic range (0.6 of shift within range (2.4-3.0 = 0.6) 2. Calculate the percent of total shift within therapeutic range (0.6/0.8 = 75%) 3. estimate number of days since last visit that were within range (75% x 16 days since last visit = 0.75 x 16 = 12 days within range, 4 days out of range) Percentage for that time period is 75% in range, and 12 total days in range.
0) We need to exclude Patients with less than 5 measurements (PT_ID repeated <5 times)
1) We need to sort data ascending for both PT_ID and Date.
2) Calculate the time difference (days) between every test for each Patient
3) Calculate the difference between each test value
4) Calculate the fraction of time each patient was within the specified range (2-3)
I can see the end result as report where rows are represented by non-duplicate PT_ID with a value of percentage of time each patient was within the INR range (2-3).
14 freelancers are bidding on average $116 for this job
Hi. I have the required skills and knowledge to satisfy your needs. I have made several Access databases so far. Please check your messages inbox for more details.