Tricky Calculation Question

[knowshowrosegrows]

I have a database that tracks daily census information for a group of service
providers. It is a pretty simple thing. For an example, a group home has a
capacity (CAP) of 12 and every day they report what their census is - say 11.
I do utilization reports where I divide their capacity by their census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the capacity for
their program. That capacity is fixed in time and should not change their
utilization percentage for all the days they reported before we changed their
capacity.

Below is the sql for the query I use to make a utilization report. Can
someone suggest ways I can relate the capacity to the date?

 

[Dale Fye]

you forgot the query.

What I would do is have an additional table that contains the ProviderID,
Capacity, CapStartDate, and CapEndDate. When you start a new capacity, you
give it a start date, then leave the end data as NULL.

Then you create a query that contains the census values and this Capacity
table. Something like:

SELECT Census.ProviderID,
Average(Census.[Census]/Capacity.[Capacity]) as Utilization
FROM Census
INNER JOIN Capacity
ON CENSUS.ProviderID = Capacity.ProviderID
WHERE Census.CensusDate >= Capacity.CapStartDate
AND Census.CensusDate <= Capacity.CapEndDate
GROUP By Census.ProviderID

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[MGFoster]

I have a database that tracks daily census information for a group of service
providers. It is a pretty simple thing. For an example, a group home has a
capacity (CAP) of 12 and every day they report what their census is - say 11.
I do utilization reports where I divide their capacity by their census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the capacity for
their program. That capacity is fixed in time and should not change their
utilization percentage for all the days they reported before we changed their
capacity.

Below is the sql for the query I use to make a utilization report. Can
someone suggest ways I can relate the capacity to the date?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Make a table of the capacities per date. Then use that table when calc.
the percentages.

E.g.: Capacities table:
Fields: group_home_id
start_date
end_date
capacity

Query example:

SELECT N.group_home_id, C.capacity,
C.capacity/N.pop_count As Utilization
FROM Capacities As C, Census As N

WHERE census_date BETWEEN C.start_date And C.end_date
AND N.group_home_id = C.group_home_id

The WHERE clause identifies which Capacities record to use.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTg1P4echKqOuFEgEQL50QCggXe4IgeMy4vxjxGhGGrRBZB72kwAoLj6
cINjvJ2EgkiIhZBNqAnJEy2J
=I0mo
-----END PGP SIGNATURE-----

 

[knowshowrosegrows]

Sorry about that. Thanks so much for replying.

I have created a table called with the following fields:
CAPDate_ID
CAPStartDate
CAPEndDate
CAP
DPASCode

Here is the SQL as it currently stands.

SELECT tblProgramComplete_MH.DPAS_Code, tblProgramComplete_MH.CAP,
tblCensusEvent_MH.Census, tblCensusEvent_MH.CensusDate,
tblCensusEvent_MH.Census_ID,
tblCensusEvent_MH.Census/tblProgramComplete_MH.CAP AS Utilization
FROM tblProgramComplete_MH INNER JOIN tblCensusEvent_MH ON
tblProgramComplete_MH.DPAS_Code = tblCensusEvent_MH.DPAS_Code;

--
Thanks

You all are teaching me so much

you forgot the query.

What I would do is have an additional table that contains the ProviderID,
Capacity, CapStartDate, and CapEndDate. When you start a new capacity, you
give it a start date, then leave the end data as NULL.

Then you create a query that contains the census values and this Capacity
table. Something like:

SELECT Census.ProviderID,
Average(Census.[Census]/Capacity.[Capacity]) as Utilization
FROM Census
INNER JOIN Capacity
ON CENSUS.ProviderID = Capacity.ProviderID
WHERE Census.CensusDate >= Capacity.CapStartDate
AND Census.CensusDate <= Capacity.CapEndDate
GROUP By Census.ProviderID

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I have a database that tracks daily census information for a group of service
providers. It is a pretty simple thing. For an example, a group home has a
capacity (CAP) of 12 and every day they report what their census is - say 11.
I do utilization reports where I divide their capacity by their census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the capacity for
their program. That capacity is fixed in time and should not change their
utilization percentage for all the days they reported before we changed their
capacity.

Below is the sql for the query I use to make a utilization report. Can
someone suggest ways I can relate the capacity to the date?

 

[knowshowrosegrows]

Dale

I used the following language
SELECT qryUtilization1.Prm_Code, qryUtilization1.Census,
qryUtilization1.CensusDate, [tblCapacity].[Cap]/[qryUtilization1].[Cap] AS
Utilization
FROM qryUtilization1 INNER JOIN tblCapacity ON qryUtilization1.Prm_Code =
tblCapacity.Prm_Code
WHERE (((qryUtilization1.CensusDate)>=[tblCapacity].[CapStartDate] And
(qryUtilization1.CensusDate)<=[tblCapacity].[CapEndDate]));

The query works fine and gives the appropriate number of records when I run
it without the WHERE statement but I get no results with the where statement.
The tblCapacity has all my programs with a CapStartDate of 10/1/2004 and a
null CapEndDate because the Cap is still current.

Ideas?

Thanks
--
Thanks

You all are teaching me so much

you forgot the query.

What I would do is have an additional table that contains the ProviderID,
Capacity, CapStartDate, and CapEndDate. When you start a new capacity, you
give it a start date, then leave the end data as NULL.

Then you create a query that contains the census values and this Capacity
table. Something like:

SELECT Census.ProviderID,
Average(Census.[Census]/Capacity.[Capacity]) as Utilization
FROM Census
INNER JOIN Capacity
ON CENSUS.ProviderID = Capacity.ProviderID
WHERE Census.CensusDate >= Capacity.CapStartDate
AND Census.CensusDate <= Capacity.CapEndDate
GROUP By Census.ProviderID

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I have a database that tracks daily census information for a group of service
providers. It is a pretty simple thing. For an example, a group home has a
capacity (CAP) of 12 and every day they report what their census is - say 11.
I do utilization reports where I divide their capacity by their census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the capacity for
their program. That capacity is fixed in time and should not change their
utilization percentage for all the days they reported before we changed their
capacity.

Below is the sql for the query I use to make a utilization report. Can
someone suggest ways I can relate the capacity to the date?

 

[Dale Fye]

Change the WHERE clause to:

WHERE qryUtilization1.CensusDate >= tblCapacity.CapStartDate
AND qryUtilization1.CensusDate <= NZ(tblCapacity.CapEndDate, Date() + 1)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Dale

I used the following language
SELECT qryUtilization1.Prm_Code, qryUtilization1.Census,
qryUtilization1.CensusDate, [tblCapacity].[Cap]/[qryUtilization1].[Cap] AS
Utilization
FROM qryUtilization1 INNER JOIN tblCapacity ON qryUtilization1.Prm_Code =
tblCapacity.Prm_Code
WHERE (((qryUtilization1.CensusDate)>=[tblCapacity].[CapStartDate] And
(qryUtilization1.CensusDate)<=[tblCapacity].[CapEndDate]));

The query works fine and gives the appropriate number of records when I run
it without the WHERE statement but I get no results with the where statement.
The tblCapacity has all my programs with a CapStartDate of 10/1/2004 and a
null CapEndDate because the Cap is still current.

Ideas?

Thanks
--
Thanks

You all are teaching me so much

you forgot the query.

What I would do is have an additional table that contains the ProviderID,
Capacity, CapStartDate, and CapEndDate. When you start a new capacity, you
give it a start date, then leave the end data as NULL.

Then you create a query that contains the census values and this Capacity
table. Something like:

SELECT Census.ProviderID,
Average(Census.[Census]/Capacity.[Capacity]) as Utilization
FROM Census
INNER JOIN Capacity
ON CENSUS.ProviderID = Capacity.ProviderID
WHERE Census.CensusDate >= Capacity.CapStartDate
AND Census.CensusDate <= Capacity.CapEndDate
GROUP By Census.ProviderID

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

I have a database that tracks daily census information for a group of service
providers. It is a pretty simple thing. For an example, a group home has a
capacity (CAP) of 12 and every day they report what their census is - say 11.
I do utilization reports where I divide their capacity by their census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the capacity for
their program. That capacity is fixed in time and should not change their
utilization percentage for all the days they reported before we changed their
capacity.

Below is the sql for the query I use to make a utilization report. Can
someone suggest ways I can relate the capacity to the date?

 

[knowshowrosegrows]

Fantastic - magical - worls perfect.

Thanks
--
Thanks

You all are teaching me so much

Change the WHERE clause to:

WHERE qryUtilization1.CensusDate >= tblCapacity.CapStartDate
AND qryUtilization1.CensusDate <= NZ(tblCapacity.CapEndDate, Date() + 1)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Dale

I used the following language
SELECT qryUtilization1.Prm_Code, qryUtilization1.Census,
qryUtilization1.CensusDate, [tblCapacity].[Cap]/[qryUtilization1].[Cap] AS
Utilization
FROM qryUtilization1 INNER JOIN tblCapacity ON qryUtilization1.Prm_Code =
tblCapacity.Prm_Code
WHERE (((qryUtilization1.CensusDate)>=[tblCapacity].[CapStartDate] And
(qryUtilization1.CensusDate)<=[tblCapacity].[CapEndDate]));

The query works fine and gives the appropriate number of records when I run
it without the WHERE statement but I get no results with the where statement.
The tblCapacity has all my programs with a CapStartDate of 10/1/2004 and a
null CapEndDate because the Cap is still current.

Ideas?

Thanks
--
Thanks

You all are teaching me so much

you forgot the query.

What I would do is have an additional table that contains the ProviderID,
Capacity, CapStartDate, and CapEndDate. When you start a new capacity, you
give it a start date, then leave the end data as NULL.

Then you create a query that contains the census values and this Capacity
table. Something like:

SELECT Census.ProviderID,
Average(Census.[Census]/Capacity.[Capacity]) as Utilization
FROM Census
INNER JOIN Capacity
ON CENSUS.ProviderID = Capacity.ProviderID
WHERE Census.CensusDate >= Capacity.CapStartDate
AND Census.CensusDate <= Capacity.CapEndDate
GROUP By Census.ProviderID

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I have a database that tracks daily census information for a group of service
providers. It is a pretty simple thing. For an example, a group home has a
capacity (CAP) of 12 and every day they report what their census is - say 11.
I do utilization reports where I divide their capacity by their census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the capacity for
their program. That capacity is fixed in time and should not change their
utilization percentage for all the days they reported before we changed their
capacity.

Below is the sql for the query I use to make a utilization report. Can
someone suggest ways I can relate the capacity to the date?

 

[Dale Fye]

Do you understand why that works?
You were comparing a date to a NULL, and that comparison will always be
false, since NULL means unknown quantity.

So, to get that to work, you have to convert the NULL to something, using
the NZ( ) function, which accepts a 2 parameters. The first is the value to
check. If this value is not null, then NZ( ) will return that value. If
however, the first parameter is NULL, the the function will return the
second (optional) parameter you pass to it. If you leave this parameter
off, the function is supposed to return a zero, but I always pass it
something.

Dale

Fantastic - magical - worls perfect.

Thanks
--
Thanks

You all are teaching me so much

Change the WHERE clause to:

WHERE qryUtilization1.CensusDate >= tblCapacity.CapStartDate
AND qryUtilization1.CensusDate <= NZ(tblCapacity.CapEndDate, Date() + 1)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Dale

I used the following language
SELECT qryUtilization1.Prm_Code, qryUtilization1.Census,
qryUtilization1.CensusDate, [tblCapacity].[Cap]/[qryUtilization1].[Cap]
AS
Utilization
FROM qryUtilization1 INNER JOIN tblCapacity ON qryUtilization1.Prm_Code
=
tblCapacity.Prm_Code
WHERE (((qryUtilization1.CensusDate)>=[tblCapacity].[CapStartDate] And
(qryUtilization1.CensusDate)<=[tblCapacity].[CapEndDate]));

The query works fine and gives the appropriate number of records when I
run
it without the WHERE statement but I get no results with the where
statement.
The tblCapacity has all my programs with a CapStartDate of 10/1/2004
and a
null CapEndDate because the Cap is still current.

Ideas?

Thanks
--
Thanks

You all are teaching me so much


:

you forgot the query.

What I would do is have an additional table that contains the
ProviderID,
Capacity, CapStartDate, and CapEndDate. When you start a new
capacity, you
give it a start date, then leave the end data as NULL.

Then you create a query that contains the census values and this
Capacity
table. Something like:

SELECT Census.ProviderID,
Average(Census.[Census]/Capacity.[Capacity]) as
Utilization
FROM Census
INNER JOIN Capacity
ON CENSUS.ProviderID = Capacity.ProviderID
WHERE Census.CensusDate >= Capacity.CapStartDate
AND Census.CensusDate <= Capacity.CapEndDate
GROUP By Census.ProviderID

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I have a database that tracks daily census information for a group
of service
providers. It is a pretty simple thing. For an example, a group
home has a
capacity (CAP) of 12 and every day they report what their census
is - say 11.
I do utilization reports where I divide their capacity by their
census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the
capacity for
their program. That capacity is fixed in time and should not
change their
utilization percentage for all the days they reported before we
changed their
capacity.

Below is the sql for the query I use to make a utilization report.
Can
someone suggest ways I can relate the capacity to the date?

 

[John Spencer]

Well, to be picky

SomeDateField = Null will return NULL which is neither true nor false.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

Pick away, John. Your feedback is always welcome.

Merry Christmas
--

Dale

email address is invalid
Please reply to newsgroup only.