Number between 2 dates - second attempt

[Joey_S]

Hello again,

I've asked before, but I'm not sure Access can do what I want.

I know about DateDiff() & it doesn't do it for me.

I want a report with each calendar day as a column heading. I want the number in hospital as the value. I only have the start and end date, is there a way of filling the gap, ie creating a value for each date between the two dates? IE showing "1" as the value for 2nd, 3rd and 4th, when I have a record with 2nd as the start date and 4th as the end date.

Thanking you.

 

[Rich]

Use a crosstab query

 

[Joey_S]

Thank u for responding. Might just be me being stupid, but...

what do I use as a column heading? If I use Start Date, it'll give me number of admissions per day. If I use End Date, it'll give me discharges per day. I don't want either. How do I get a count of people for each day inbetween? Bearing in mind we get thousands of people a month with different start and end dates.

Getting the feeling it can't be done...

 

[ColinEssex]

Hi Joey

I get the impression that you work for the good old NHS - Information dept. by any chance???
I think what you are trying to do is a parameterised report on bed occupancy. You want to select a month and have the dates across the top and the wards or specialties or whatever down the side?
Before I go on, am I on the right track?

Email me if you prefer
Col

 

[Joey_S]

Hi Col,
Well, yes, I do work for an NHS Info Dept, and thats exactly the report we need! We used to have one set up in our PAS, but we changed PAS system last year and haven't been able to report on occupancy since.

You're an absolute star if you know a way to do this.

Joey.

 

[simongallop]

Easy way is use Excel. Hard way is use code.

EXCEL: Open a new workbook. First bit is the cell reference then either TEXT or =formula. Copy them in excatly and it will work. Am using 1st Jan 2002 as the first date under test but you can choose what you want!

A1 START DATE
B1 END DATE
C1 01/01/02
D1 =c1+1

C2 =if(and(c$1>=$a2,c$1<=$b2),1,0)

Copy the formula in D1 as far to the right as you want (max of 253 days as excel has 255 columns!)
Enter you dates in columns A and B and copy the formula in C2 to cover the area of the grid.
At the bottom of the grid sum each column to show occupancy.

CODE:
Get a code to create a table with the dates that you want with 2 columns being Date and Beds. Make the dates consecutive and the beds 0. Then open the table with the data and go through each record and if a bed is occupied on a certain date then add 1 to that dates record.

One thought though. If someone leave on the 2nd and someone arrives on the 2nd you will have a double count though there aren't 2 people in that bed!

HTH

 

[ColinEssex]

Hi Joey,

Firstly there should be a standard report on your PAS that does this - however if not then try this. I recommend you do a few sample patients so that you get it right.
We're also talking about complete hospital spells not FCE's.
OK - what you need to do is download the patient data to include PatID,Specialty,Ward,AdmDate,DisDate
then you need to create a new table to include those fields PLUS a new field called Date2 or something. Date2 is going to be the next date in the sequence - e.g. patient1 admitted 01/04/02 and disch 04/04/02 will look like this
PatID AdmDate Date2 DisDate Spec
123 01/04 01/04 04/04 13
123 01/04 02/04 04/04 13
123 01/04 03/04 04/04 13
123 01/04 04/04 04/04 13

You then write a function that calls the original downloaded datatable, use a for-next loop between admDate and EndDate and AddNew the data to the 2nd table. For every loop you need to add 1 to the AdmDate to give you the Date2 date. Having got the "in between" dates (date2) into a table you can now do your crosstab or whatever based on that field. Obviously you will have included hospital/specialty/ward or whatever to give you the breakpoint. It looks even better if you do pivot tables in Excel.
When I did it I emptied the 2nd table before each "run" because there is so much data to post.

Good luck
Col

 

[Joey_S]

Wow, thanks Harry and Col.

I shall have a play and let you know.

Yes, PAS should have this report set up. But don't get me started on that one...

Thanks guys
Jo.

 

[Joey_S]

Okay, sorry to ask for help again, but...

The excel solution works great - nice and simple, just like me. However, if I want to analyse several wards, it gets a little unwieldy. I can use it for totals and one ward at a time, so thanks for the suggestion.

Now, it seems I need to use this Looping lark, which I confess I know nothing about. Any chance you can give me an example of the code?

Really appreciate the help

 

[Liz H]

I am in the same dilemma. I need a count of patient days by month. I've got total patient days set up also I have a report that I generate for a daily census. I need to be able to look at with patient by any day. If I have to I can purchase a program for that only. Any suggestions?

 

[simongallop]

Can't remember how to make a new table in code so create a new table with the following fields (plus whatever else you need): Patient_ID, Date_In_Hosp, Ward_No, etc... Call table Pat_Expand. Will presume original table is called Patients!!

Dim rstOrig, rstNew as Recordset
Dim SDate, EDate, TestDate as Date

Sub Expand_Date()
Set rstOrig = CurrentDB.OpenRecordset("Patients",dbOpenDynaset)
Set rstNew = CurrentDB.OpenRecordset("Pat_Expand",dbOpenDynaset)

rstOrig.MoveFirst
Do
SDate=rstOrig("PatientInDateField")
EDate=rstOrig("PatientOutDateField")
TestDate = SDate
Do
rstNew.AddNew
rstNew("Patient_ID")=rstOrig("Patient_ID")
rstNew("Date_In_Hosp")=TestDate
rstNew("Ward_No") = rstOrig("Ward_No") 'Presume field is called Ward_No in original table
rstNew("otherfields")=etc..
rstNew.Update
TestDate = TestDate + 1
loop until TestDate > EDate
rstOrig.MoveNext
Loop Until rstOrig.EOF = True
rstOrig.close
rstNew.close
End Sub

HTH

 

[simongallop]

Oh forgot a few things! Presuming that all fields have an end date otherwise it will go on forever. To stop it then where the EDate is assigned, run a test on it ie If IsNull(EDate) then GoTo NextLoop End If

and place NextLoop: (note the colon) in the line before rstOrig.MoveNext

The code will then have expanded the dates and you can now run a Totals query on the result grouping by WardNo and Date.

HTH

(ps: As I am doing work for the NHS does this mean that I get my 1p NIC contribution back?)

 

[ColinEssex]

Liz - Patient days by month (or occupied bed days) If you need this figure, it's the patients length of stay all added together. Don't forget to watch out for where patients are in over monthEnd and monthBeginning.

As for a census, there is a table on your PAS where it holds current inpatients, just use that, and there is also some standard PAS reports that do this (for switchboard or Chaplains etc)

Hope this helps
Col

 

[Joey_S]

Oh my God, it worked. Harry, you are a star! Sorry about your 1p though.

D'ya think we should have a NHS Information Forum? Do we have one already?

Thanks guys, that's made my day.
Joey.

 

[Liz H]

Thanks! I don't work for the NHS, just a little hospital in Louisiana! But it worked for my dilemma