Running balance report help needed

[Dophia]

Hi Everyone:
I would appreciate any help/guidance that anyone could give me about keeping a running balance which will be used to keep a drug log.

I have one table which keeps track of the date each of three drugs are used and the dosage used.
Date
Drug 1
Drug 2
Drug 3

Now to keep a drug log of the usage, should I put the purchases of the drugs in another table or in the same table? How would I get a balance of the drugs on hand? Would you always have to calculate the entire table to get a balance on hand, or is there a way to calculate the balance for a certain period of time?
Help!
Sophia

 

[THWatson]

Sophia
Here's the way I would do it...

Create 3 tables

tblDrugs
DrugID (AutoNumber, Primary Key)
Drug (text)

tblDrugPurchase
PurchaseID (AutoNumber, Primary Key)
DrugID (Number, foreign key related to DrugID in tblDrugs)
QuantityPurchased (Number)
PurchaseDate (Date/time)

tblDrugUsed
UsedID (AutoNumber, Primary Key)
DrugID (Number, foreign key related to DrugID in tblDrugs)
QuantityUsed (Number)
UsedDate (Date/Time)

Then you can use a query to get a balance at any point in time. You can also get the quantity used between any two given dates.

Tom

 

[Dophia]

Thanks for your suggestion Tom! But, how would you get the quantity on hand at any given point? Wouldn't you have to take into account the entire history of purchases and usage? If so, how can I put this in a report without printing the whole history? For example, if the purchases and usage start in Jan 2005, how can I print the report from Aug 1-31 2006 without having to print everything prior to that date?

Sophia

 

[THWatson]

Sophia
Three issues...

To get the balance on hand at any given point in time, is it not Purchases minus Usage to that point? You can calculate that in a query and then base your report on that query.

To get the usage from Aug. 1/06 to Aug. 31/06, it's Usage between those two dates. Again, your query can provide that information.

To get purchases and usage from Aug. 1/06 to Aug. 31/06, it's Purchases minus Usage between those two dates.

I would create a form to enter the drugs, purchases and usage.

I would also create an unbound form in which you have two unbound date text boxes - txtStartDate and txtEndDate. These text boxes would be used to provide the date parameters between which you want the usage. Also on the form would be a command box to run the report which is based on the query.
Whether you hardwire the query to the StartDate and EndDate text boxes, or put code behind the command button that refers to those text boxes, is a matter of preference and how many queries you want to build.

If there's something here that needs more explanation, post back.

Tom

 

[Dophia]

I'm still having problems with this. Any help would be greatly appreciated.

The one problem that I have is that the drug log that I have accumulated the data in keeps track of the three drugs based on each patient. My table is as follows:

tblDrugLog
Log_ID: Primary key AutoNo
Patient_No: Primary key related to tblPatient
Date_used: date
DrugA: Dosage used
DrugB: Dosage used
DrugC: Dosage used


How can I incorporate the purchase of the drugs with the usage of the drugs and create a running balance of the three drugs?

Sophia