Mark01
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_Da te.TestDat e},”1/01/2 018, 1/02/2018, 1/02/2018”)/count({Aspirin _Dosage.
Dosage},{Test_Date.TestDat e}, ”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?
Daily_Dosage2.rpt
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
Dosage},{Test_Date.TestDat
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?
Daily_Dosage2.rpt
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_Da te.TestDat e},”1/01/2 018, 1/02/2018, 1/02/2018”)/count({Aspirin _Dosage.
Dosage},{Test_Date.TestDat e}, ”1/01/2018, 1/02/2018, 1/02/2018”)
Can the dates be specified in a formula like the following:
sum({Aspirin_Dosage.Dosage
Dosage},{Test_Date.TestDat
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ASKER
Here is the report file.
Daily_Dosage2.rpt
Daily_Dosage2.rpt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, mlmcc and Raghavendra.
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?