medication calculator

[Elmobram22]

Hi all,

I am wondering of a way to create a calculator for adding up how much medication someone has at any given time.

For example.

If Bill collects 28 days supply of medication on a monday morning consisting of...
Paracetamol 500mg x 224 (two to be taken at breakfast, dinner, teatime and bedtime)
Ibuprofen 200mg x 56 (one to be taken breakfast and bedtime)
Vitamin D tablet x 28 (one to be taken at bedtime)

I want a calculation so if i go to spot check his medication on the 12th day of the 28 day cycle at 6pm it can calculate exactly how many of each medication he should have left. As for that day, at 6pm he would have already taken 6 paracetamols and 1 ibuprofen although if I had checked at 10am then he would have taken two paracetamol and one ibuprofen. I know this is feesable to do but could do with a starting block as to the best way to go about this.

I currently have all the data within a database but no real links to times or dates.

Cheers,

Paul

 

[Cronk]

For a start you need to define a link between the times of the events (breakfast, dinner, teatime and bedtime) if your query is to be based on a specific time eg at 6pm.

So breakfast is defined as say 8 am. But then, what happens if I don't eat then, earlier or later.

 

[Elmobram22]

Breakfast would be 9.30am
Lunchtime 1.30pm
Teatime 5.30pm
Bedtime 9.30pm

That's the set times I would be using.

 

[Cronk]

So let's say for a medication with the number consumed at each time period
ie for MedicationID quantity used in each time period is Q1, Q2, Q3, Q4

The quantity of tablets remaining would be
StartQuantity - (Q1 + Q2 + Q3 +Q4) * Number of whole days - (Q1 * time > period 1) - ( Q2 * time > Period 2) etc