Link to home
Start Free TrialLog in
Avatar of Mark01
Mark01Flag for United States of America

asked on

Quantity and Date Range Calculations

I am working on a formula that calculates the number of milligrams of aspirin that a patient consumes daily. A patient consumes varying amounts of aspirin daily. A blood test determines the concentration of aspirin in the patient. The test results are always fluctuating.

Here is some sample data:
Patient takes the following quantity of aspirin from January 1, 2018 to January 3, 2018: 1-1-18: 1 mg of aspirin;
1-2-18: 2 mg and 1-3-18: 3 mg.

Calculation: 6 divided by 3 = 2 mg in 3 day period. Result is 2 mg per day.

Here is the formula that I am trying to work with:
sum({Aspirin_Dosage.Dosage},{Test_Date.TestDate},”1/01/2018, 1/02/2018, 1/02/2018”)/count({Aspirin_Dosage.
Dosage},{Test_Date.TestDate}, ”1/01/2018, 1/02/2018, 1/02/2018”)

Database fields (Access):
{Test_Date.TestDate} = Need 3 days.
{Aspirin_Dosage.Dosage} = Need to add daily dose in mg for 3 days.

Report = The attached report is grouped on the {Test_Date.TestDate} field and “for each day” is selected under “the section will be printed” option under “Options”.

Screenshots of the database tables are shown below. How do I write a formula that performs the calculations?
 User generated imageDaily_Dosage2.rpt
Avatar of Raghavendra Hullur
Raghavendra Hullur
Flag of India image

Hi,

How will you ensure you are calculating the average for 3 days only? Are you filtering the data for a particular period and want to get average for that period?
Avatar of Mark01

ASKER

I want to use a formula to calculate the average for 3 days. Is that possible? I am not doing any filtering.

Can the dates be specified in a formula like the following:

sum({Aspirin_Dosage.Dosage},{Test_Date.TestDate},”1/01/2018, 1/02/2018, 1/02/2018”)/count({Aspirin_Dosage.
Dosage},{Test_Date.TestDate}, ”1/01/2018, 1/02/2018, 1/02/2018”)
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark01

ASKER

I tried using mlmcc's formulas, but the average is not correct. The report is attached. Here are some screenshots.
User generated image
Avatar of Mark01

ASKER

Here is the report file.
Daily_Dosage2.rpt
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark01

ASKER

Thank you, mlmcc and Raghavendra.