How to calculate the average for a daily changing value?

[healthcare executive 72]

I built a Microsoft Access 2000 Database for the inpatient department in my
hospital. The main table has fields for patients' personal identification
data as well as fields for admission and discharge dates. The "current
status" report is based on a query that has the criteria: discharge date Is
Null (Logic: no discharge date = patient is still in, right?). The report has
a counter (so this counter shows the number of patients who are still in at
any given time). This counter keeps changing (whenever a discharge date is
entered for a patient, he is automatically removed from the "current status"
report, and whenever a new patient is admitted he is added to the report).
How can I store daily values for this counter (e.g. at 9:00 AM every morning)
so I can calculate the Average daily census over a period of time?

 

[Sharkbyte]

Sorry, I know there is a timer function that will probably meet your needs,
but I have no experience with it. If you haven't already, check the help
files. Or consider having it done as a function of some daily housekeeping
tasks.

Sharkbyte

 

[KARL DEWEY]

Build a number list table (Count) with one through however many dates your
report will contain (7 for week, 31 for month, 90 for quarter) – Field Size –
Double

First query –
SELECT DateValue([Enter date]) AS [Date Checked], Count.Count
FROM [Count];

Second query –
SELECT [Date Checked]-[Count] AS [Hospital Stay], Count(YourTable.ID) AS
Patients
FROM [Query2-1], YourTable
WHERE ((([Date Checked]-[Count]) Between [AdmitDate] And IIF([DischargeDate]
Is Null, Date(),[DischargeDate])))
GROUP BY [Date Checked]-[Count];

 

[Jack Marks]

At least one of the big databases (I am talking about Lawson here) would not
just leave the discharge date blank, but use a dummy date of 1/1/1700,
meaning they have not been discharged yet. They use this with
"Termination Date" in the employee data base. There MUST be a reason why
they do this - possibly so that they can do the kind of calculation you are
talking about. This would totally remove the whole null problem.
"healthcare executive 72" <healthcare executive
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Jesse Aviles]

If the counter comes from a query, you can store the counter value in table that will only be used
to hold the date and counter number. You can use VBA code to run the query automatically at 0900
everyday or have someone do it. If you use VBA code to do it automatically, the database has to be
open, otherwise it won't work. Then you can run a report or export to Excel and calculate
statistical analysis on the amount of "inpatients" over a period of time.