Sorry to take so long, but I've been trying to come up with a simple
solution and I am not having any luck.
My solution involves adding a new table that contains one column.
Table: tDates
Field: fDate
You need one record in that table for every date in the time period you are
interested in.
Then you can use a query to create a "record" for each day and then get a
count for each date
--Add your table and the tDates table to a new query
--Add BMC_ID and fDate to the fields
--Under fDate enter the followng as criteria
Between AdmitDate and DischargeDate
-- Select Query: Totals from the menu
-- Change GROUP BY to Count for BMC_ID
-- Change GROUP BY to WHERE under fDate
-- Add fDate to the query again.
That will give you a count for each day.. Save this query as qBase
Now using Qbase as the source for the next query.
-- Add Qbase to the query
-- Add fDate and CountOfBMC_ID to the query
-- Add fDate again
-- select Query: Totals
-- Change Group by to COUNT under one of the Fdate
-- Change to other Fdate to Format(qBase.Fdate,"yyyy-mm")
-- Change Group By to WHERE under CountOfBM_ID and
-- Enter the criteria
I think that will give you the information you are looking for.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Ok, your original posting indicated that you already had calculated the
number of residents in the facility on each date. Your fields don't
indicate that.
SO the first question is have you already calculated the number of
people in the facility for each day. Or are you planning to calculate
them using the AdmitDate and Discharge Date? If so, do you have a table
of dates in your database?
I hesitate to go any further with a solution until I understand more
about your data.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
AccessNeophyte wrote:
SELECT Format([Date],"yyyy-mm")
, Abs(Sum([ResidentCount]/27 >=.9)) as DaysFilled
FROM YourTable
WHERE [Date] Between #2006-1-1# and #2007-12-31#
GROUP BY Format([Date],"yyyy-mm")
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
AccessNeophyte wrote:
Hi All,
I'v browsed this group for an answer to this, with no success.
This is my situation:
I need to end up with the number of days during a given month on
which
the total number of residents ("Occupancy") meets or exceeds 90% of
our Capacity of 27.
I've done this with an Excel spreadsheet, summing these columns:
Col A Col B Col C Col D
Date ~ # of residents ~ % of Capacity ~ 0 or 1, depending
="Col B"/27 ~ =IF("Col C"
0.9,1,0)
Is there an expression(s) that will give me the total from Col D?
Thanks,
Liz- Hide quoted text -
- Show quoted text -
Thanks John - I always enjoy your posts.
You give me credit for being farther along in my understanding of
Access than I am. Is it possible to get this information in the
format of what expressions I would use in a query, in Query Design
view, rather than SQL view?
Would it help if I were to send the spreadsheet I currently use? I
don't know If I was clear about what the columns contained.
Column A contains each day of the month in question.
Column B contains (I believe) what you have called [ResidentCount] for
each date.
If it helps, the fields from my table that I'm trying to use in my
query are:
BMC_ID (pk)
AdmitDate
DischargeDate
Thanks,
Liz- Hide quoted text -
- Show quoted text -
SO the first question is have you already calculated the number of
people in the facility for each day.
Liz - No
Or are you planning to calculate them using the AdmitDate and
Discharge Date?
Liz - Hoping to, yes.
If so, do you have a table of dates in your database?
Liz - Unclear what you mean by "table of dates." I have fields in my
"Client Data" table for AdmitDate & DischargeDate. I don't think
that's what you mean, though.- Hide quoted text -
Thanks John,
Is there a simple way to enter all the dates from 1/1/2007 forward
into this table? I can't find anything helpful in Help or in
microsoft.public.access.tabledesign.
Liz- Hide quoted text -
- Show quoted text -
Update!!
Here is the SQL for qBase:
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID,
tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate]
And [BMC Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate;
Here is the SQL for the second query, which I called qBase2:
SELECT Count(qBase.fDate) AS CountOffDate, Format(qBase.fDate,"yyyy-
mm") AS Expr1
FROM qBase
WHERE (((qBase.CountOfBMC_ID)>0.9*27))
GROUP BY Format(qBase.fDate,"yyyy-mm");
Is this correct?
BTW, for the dates table, I just did an autofill in Excel (all 65536
lines!) and imported it. That gave me dates through 6/6/2186. That
ought to hold us for a while. ;o}
I have another query I set up to show me the Total Occupancy on a
given date ([Requested Date] in the query). This is what I was hoping
I would NOT have to run for each day of the month in order to get my
final total. When I run that query for several days of my test month,
some of the totals I get are different from the results of qBase.
This is the SQL for that query:
SELECT [BMC Client Data Table].BMC_ID, [BMC Client Data Table].Bed,
[BMC Client Data Table].AdmitDate, [BMC Client Data Table].Gender,
[BMC Client Data Table].LastName, [Requested Date] AS Expr1, [BMC
Client Data Table].DC_DischargeDate, [BMC Client Data
Table].FirstName, Now() AS Expr2
FROM [BMC Client Data Table]
WHERE ((([BMC Client Data Table].AdmitDate)<=[Requested Date]) AND
(([BMC Client Data Table].DC_DischargeDate) Is Null)) OR ((([BMC
Client Data Table].AdmitDate)<=[Requested Date]) AND (([BMC Client
Data Table].DC_DischargeDate)>[Requested Date]))
ORDER BY [BMC Client Data Table].Bed, [BMC Client Data
Table].AdmitDate, [BMC Client Data Table].Gender, [BMC Client Data
Table].LastName;
Any idea why I'm getting different numbers?
Thanks,
Liz
