• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

How do I create a daily census?

This seems like it should be easy, but I can't figure it out.  I have a database that includes a table with all patients admitted to our ICU including the date and time of admission and discharge from the ICU.  We need to generate a daily census between two dates (can be entered by the person running the query) to count the number of patients in the ICU on each date at a specific time (08:00 AM).

For example if we need the data from Jan 1, 2007 to Jan 31, 2007 I would like to end up with a table that has a row for each date in that range and the number of patients in the ICU at 08:00 AM on that date.

Thank you very much,

Paul Warshawsky
0
paulwarshawsky
Asked:
paulwarshawsky
  • 3
1 Solution
 
Patrick MatthewsCommented:
Hello Paul,

T make this work well, you will need another table, tblDays, that has dates in it, covering any
conceivable date you might need for the query.  Assuming you build that table (it would be
very easy to populate with VBA code), then something like this could work:

SELECT d.Dt,
    (SELECT Count(p.*)
    FROM tblPatients t
    WHERE t.AdmittedAt <= (d.Dt + TimeValue([Enter census time])) AND
        t.DischargedAt >= (d.Dt + TimeValue([Enter census time]))) AS PatientCount
FROM tblDays t
WHERE d.Dt Between [Enter start date] AND [Enter end date]
ORDER BY d.Dt

Regards,

Patrick
0
 
Patrick MatthewsCommented:
Paul,

And to use form controls rather than parameters:

SELECT d.Dt,
    (SELECT Count(p.*)
    FROM tblPatients t
    WHERE t.AdmittedAt <= (d.Dt + TimeValue([Forms]![NameOfForm]![CensusTime])) AND
        t.DischargedAt >= (d.Dt + TimeValue([Forms]![NameOfForm]![CensusTime]))) AS PatientCount
FROM tblDays t
WHERE d.Dt Between [Forms]![NameOfForm]![StartDate] AND [Forms]![NameOfForm]![EndDate]
ORDER BY d.Dt


Regards,

Patrick
0
 
paulwarshawskyAuthor Commented:
This sort of solution occurred to me as well, but I have to say it seems a bit clunky (no offense intended!)  We may need this data from 3 years ago or 5 years from now, meaning I have to have a table with a decade worth of dates in it (or update the table regularly in the future.)

The census time is fixed (8:00 AM), by the way.  Also would need to deal with patients who have not been discharged (i.e. DischargeDateTime is NUL).

Any other thoughts?

Paul
0
 
Patrick MatthewsCommented:
Paul,

No offense taken, but that is the only way to guarantee you get a response for every date
within the date range.  You could use code to build that table on the fly, but that is just a
variation on a theme.

Changing the SQL to use a fixed census time, and to allow for nulls in the discharge
(had totally forgotten about that possibility):

SELECT d.Dt,
    (SELECT Count(p.*)
    FROM tblPatients t
    WHERE t.AdmittedAt <= (d.Dt + TimeValue("8:00 AM")) AND
        ((t.DischargedAt >= (d.Dt + TimeValue("8:00 AM") OR t.DischargedAt Is Null)))) AS PatientCount
FROM tblDays t
WHERE d.Dt Between [Enter start date] AND [Enter end date]
ORDER BY d.Dt


or with a form...


SELECT d.Dt,
    (SELECT Count(p.*)
    FROM tblPatients t
    WHERE t.AdmittedAt <= (d.Dt + TimeValue("8:00 AM")) AND
        ((t.DischargedAt >= (d.Dt + TimeValue("8:00 AM") OR t.DischargedAt Is Null)))) AS PatientCount
FROM tblDays t
WHERE d.Dt Between [Forms]![NameOfForm]![StartDate] AND [Forms]![NameOfForm]![EndDate]
ORDER BY d.Dt



BTW, to populate that table, consider something like this:

Sub PopulateDates()

    Dim dt As Date

    With DoCmd
        .SetWarnings False
        .RunSQL "DELETE * FROM tblDays"
        dt = #1 Jan 1900#
        Do While dt < #31 Dec 2050#
            .RunSQL "INSERT INTO tblDays (Dt) VALUES (#" & dt & "#)"
            dt = dt + 1
        Loop
        .SetWarnings False
    End With

    MsgBox "Done"

End Sub

Regards,

Patrick
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now