Need query help

[knowshowrosegrows]

Can someone help me create a query that:

1. Searches Between [Starting Census Date] And [Ending Census Date] from
tblCensusEvent.CensusDate

2. Finds the tblDPASCap.Capacity that was EFFECTIVE (tblDPASCap.EFFECTIVE)
during the above period for each tblProgram_Details.DPAS_Code.

3. Sums the Capacity for all the tblProgram_Details.DPASCode that comprises
each tblProgram_Details.Program_ID record.

tblCensusEvent
[Program_ID] [Census_ID] [CensusDate]
111 A 3-3-2004
111 C 3-3-2005
222 B 3-3-2004
222 D 3-3-2005

tblDPASCap
[DPAS] [Capacity] [EFFECTIVE]
41663 16 1-1-2004
41663 12 1-1-2005
41663 10 1-1-2006
57891 46 1-1-2004
57891 120 1-1-2005
57891 80 1-1-2006
38806 15 1-1-2004

tblProgram_Details
[Program_ID] [DPAS_Code]
111 41663
111 57891
222 38806

So a Census Event that took place on 3-3-04 for Program_ID 111 would have a
total capacity of 62 (sum 16 and 46). If an event for Program_ID 111 took
place on 3-3-05 the capacity would come out to 132.

An Event on 3-3-04 or 3-3-05 for Program_ID 222 would have a capacity of 15.

The tricky part of the formula is choosing the tblDPASCap record that has an
EFFECTIVE that has a range that the CensusDate fall in line with.

 

[Michel Walsh]

SELECT CensusEvents.program_id, SUM(capacity)
FROM (CensusEvents INNER JOIN ProgramDetails
ON CensusEvents.programID = programDetails.programID)
INNER JOIN DPASCaps
ON ( DPASCaps.effective BETWEEN CensusEvents.[Starting Census Date]
AND CensusEvents.[Ending Census Date] )
GROUP BY CensusEvents.program_id



The ( ) are important.

I removed the tbl prefixes for readability.





Vanderghast, Access MVP

 

[knowshowrosegrows]

Thanks so much for your quick reply.

I think I did not describe my need well.

Lets take away the ability to limit the range of the censusdate that I want
records for with the BETWEEN statement.

I want a query that will take a census_ID that has a program_ID that took
place on a CensusDate) from tblCensusEvent

and go to the Program_Details table and find the DPAS_Codes that make up
that Program_ID. It will then go to the tblDPASCap table and find the
records that have a matching DPAS to the DPAS_Code. Then it will find out
what the capacity was THAT WAS IN EFFECT ON THAT CENSUSDATE. Then it will
sum all the capacities that were in effect on the CensusDate for the
Program_Id.

Is that any clearer? The part of the formula I can't imagine is how to
choose only the tblDPASCap record that has an Effective (date) that is lower
than the CensusDate but before the next higher Effective (date) that is
higher than the CensusDate. The CensusDate needs to fall between one
Effective and before another Effective so I can see which Capacity was in
effect during that range.
--
Thanks

You all are teaching me so much

SELECT CensusEvents.program_id, SUM(capacity)
FROM (CensusEvents INNER JOIN ProgramDetails
ON CensusEvents.programID = programDetails.programID)
INNER JOIN DPASCaps
ON ( DPASCaps.effective BETWEEN CensusEvents.[Starting Census Date]
AND CensusEvents.[Ending Census Date] )
GROUP BY CensusEvents.program_id



The ( ) are important.

I removed the tbl prefixes for readability.





Vanderghast, Access MVP

Can someone help me create a query that:

1. Searches Between [Starting Census Date] And [Ending Census Date] from
tblCensusEvent.CensusDate

2. Finds the tblDPASCap.Capacity that was EFFECTIVE (tblDPASCap.EFFECTIVE)
during the above period for each tblProgram_Details.DPAS_Code.

3. Sums the Capacity for all the tblProgram_Details.DPASCode that
comprises
each tblProgram_Details.Program_ID record.

tblCensusEvent
[Program_ID] [Census_ID] [CensusDate]
111 A 3-3-2004
111 C 3-3-2005
222 B 3-3-2004
222 D 3-3-2005

tblDPASCap
[DPAS] [Capacity] [EFFECTIVE]
41663 16 1-1-2004
41663 12 1-1-2005
41663 10 1-1-2006
57891 46 1-1-2004
57891 120 1-1-2005
57891 80 1-1-2006
38806 15 1-1-2004

tblProgram_Details
[Program_ID] [DPAS_Code]
111 41663
111 57891
222 38806

So a Census Event that took place on 3-3-04 for Program_ID 111 would have
a
total capacity of 62 (sum 16 and 46). If an event for Program_ID 111 took
place on 3-3-05 the capacity would come out to 132.

An Event on 3-3-04 or 3-3-05 for Program_ID 222 would have a capacity of
15.

The tricky part of the formula is choosing the tblDPASCap record that has
an
EFFECTIVE that has a range that the CensusDate fall in line with.

 

[Michel Walsh]

So,you have only one date field, as in your table description, not two, in
CensusEvent, as in your first point in your original message. Right? And the
date limits are in two different records


=====================

SELECT CensusEvent.programID,
CensusEvent.censusDate,
SUM(capacity) AS running

FROM (CensusEvent INNER JOIN ProgramDetails
ON CensusEvents.programID = ProgramDetails.programID)
INNER JOIN DPASCaps
ON DPASCaps.DPasCode = ProgramDetails.DPasCode
AND DPASCaps.Effective >= CensusEvent.censusDate

GROUP BY CensusEvent.programID, CensusEvent.censusDate

========================
can be a first query. Note that it can be

AND DPASCaps.Effective > CensusEvent.censusDate

instead of the >=.



That query returns two records per programID, with the SUM of capacity UP TO
THE DATE they are associated to. The sum you really need seems to be the
simple difference of these two 'cumulative' (or 'running') sums, which is
simple if you see those two runnings as on is the min and one is the max,
for a given programID, in the previous query, that is:


===================

SELECT programId, MAX(running)-MIN(running)
FROM previousQuery
GROUP BY programId
===================



Vanderghast, Access MVP

Thanks so much for your quick reply.

I think I did not describe my need well.

Lets take away the ability to limit the range of the censusdate that I
want
records for with the BETWEEN statement.

I want a query that will take a census_ID that has a program_ID that took
place on a CensusDate) from tblCensusEvent

and go to the Program_Details table and find the DPAS_Codes that make up
that Program_ID. It will then go to the tblDPASCap table and find the
records that have a matching DPAS to the DPAS_Code. Then it will find out
what the capacity was THAT WAS IN EFFECT ON THAT CENSUSDATE. Then it will
sum all the capacities that were in effect on the CensusDate for the
Program_Id.

Is that any clearer? The part of the formula I can't imagine is how to
choose only the tblDPASCap record that has an Effective (date) that is
lower
than the CensusDate but before the next higher Effective (date) that is
higher than the CensusDate. The CensusDate needs to fall between one
Effective and before another Effective so I can see which Capacity was in
effect during that range.
--
Thanks

You all are teaching me so much

SELECT CensusEvents.program_id, SUM(capacity)
FROM (CensusEvents INNER JOIN ProgramDetails
ON CensusEvents.programID = programDetails.programID)
INNER JOIN DPASCaps
ON ( DPASCaps.effective BETWEEN CensusEvents.[Starting Census
Date]
AND CensusEvents.[Ending Census Date] )
GROUP BY CensusEvents.program_id



The ( ) are important.

I removed the tbl prefixes for readability.





Vanderghast, Access MVP


in
message news:[email protected]...

Can someone help me create a query that:

1. Searches Between [Starting Census Date] And [Ending Census Date]
from
tblCensusEvent.CensusDate

2. Finds the tblDPASCap.Capacity that was EFFECTIVE
(tblDPASCap.EFFECTIVE)
during the above period for each tblProgram_Details.DPAS_Code.

3. Sums the Capacity for all the tblProgram_Details.DPASCode that
comprises
each tblProgram_Details.Program_ID record.

tblCensusEvent
[Program_ID] [Census_ID] [CensusDate]
111 A 3-3-2004
111 C 3-3-2005
222 B 3-3-2004
222 D 3-3-2005

tblDPASCap
[DPAS] [Capacity] [EFFECTIVE]
41663 16 1-1-2004
41663 12 1-1-2005
41663 10 1-1-2006
57891 46 1-1-2004
57891 120 1-1-2005
57891 80 1-1-2006
38806 15 1-1-2004

tblProgram_Details
[Program_ID] [DPAS_Code]
111 41663
111 57891
222 38806

So a Census Event that took place on 3-3-04 for Program_ID 111 would
have
a
total capacity of 62 (sum 16 and 46). If an event for Program_ID 111
took
place on 3-3-05 the capacity would come out to 132.

An Event on 3-3-04 or 3-3-05 for Program_ID 222 would have a capacity
of
15.

The tricky part of the formula is choosing the tblDPASCap record that
has
an
EFFECTIVE that has a range that the CensusDate fall in line with.

 

[knowshowrosegrows]

Right

There are numerous records for each DPAS in tblDPASCap. It tracks how the
Capacity has changed over time. So a Capacity became effective on the
Effective (Date) and was only in effect until another record was entered with
that DPAS. The new record has a different Capacity and Effective (Date).

I need to find out what Capacity was in effect when the CensusDate took
place.

To complicate it, tblProgramDetails shows all the different DPAS that
comprise a single Program_ID. So I need to Sum the Capacities associated
with those different DPAS that were in Effect for a Program_ID on a
CensusDate. Then I know the true Capacity that was in effect on the Census
Date.
--
Thanks

You all are teaching me so much

So,you have only one date field, as in your table description, not two, in
CensusEvent, as in your first point in your original message. Right? And the
date limits are in two different records


=====================

SELECT CensusEvent.programID,
CensusEvent.censusDate,
SUM(capacity) AS running

FROM (CensusEvent INNER JOIN ProgramDetails
ON CensusEvents.programID = ProgramDetails.programID)
INNER JOIN DPASCaps
ON DPASCaps.DPasCode = ProgramDetails.DPasCode
AND DPASCaps.Effective >= CensusEvent.censusDate

GROUP BY CensusEvent.programID, CensusEvent.censusDate

========================
can be a first query. Note that it can be

AND DPASCaps.Effective > CensusEvent.censusDate

instead of the >=.



That query returns two records per programID, with the SUM of capacity UP TO
THE DATE they are associated to. The sum you really need seems to be the
simple difference of these two 'cumulative' (or 'running') sums, which is
simple if you see those two runnings as on is the min and one is the max,
for a given programID, in the previous query, that is:


===================

SELECT programId, MAX(running)-MIN(running)
FROM previousQuery
GROUP BY programId
===================



Vanderghast, Access MVP

Thanks so much for your quick reply.

I think I did not describe my need well.

Lets take away the ability to limit the range of the censusdate that I
want
records for with the BETWEEN statement.

I want a query that will take a census_ID that has a program_ID that took
place on a CensusDate) from tblCensusEvent

and go to the Program_Details table and find the DPAS_Codes that make up
that Program_ID. It will then go to the tblDPASCap table and find the
records that have a matching DPAS to the DPAS_Code. Then it will find out
what the capacity was THAT WAS IN EFFECT ON THAT CENSUSDATE. Then it will
sum all the capacities that were in effect on the CensusDate for the
Program_Id.

Is that any clearer? The part of the formula I can't imagine is how to
choose only the tblDPASCap record that has an Effective (date) that is
lower
than the CensusDate but before the next higher Effective (date) that is
higher than the CensusDate. The CensusDate needs to fall between one
Effective and before another Effective so I can see which Capacity was in
effect during that range.
--
Thanks

You all are teaching me so much

SELECT CensusEvents.program_id, SUM(capacity)
FROM (CensusEvents INNER JOIN ProgramDetails
ON CensusEvents.programID = programDetails.programID)
INNER JOIN DPASCaps
ON ( DPASCaps.effective BETWEEN CensusEvents.[Starting Census
Date]
AND CensusEvents.[Ending Census Date] )
GROUP BY CensusEvents.program_id



The ( ) are important.

I removed the tbl prefixes for readability.





Vanderghast, Access MVP


in
message Can someone help me create a query that:

1. Searches Between [Starting Census Date] And [Ending Census Date]
from
tblCensusEvent.CensusDate

2. Finds the tblDPASCap.Capacity that was EFFECTIVE
(tblDPASCap.EFFECTIVE)
during the above period for each tblProgram_Details.DPAS_Code.

3. Sums the Capacity for all the tblProgram_Details.DPASCode that
comprises
each tblProgram_Details.Program_ID record.

tblCensusEvent
[Program_ID] [Census_ID] [CensusDate]
111 A 3-3-2004
111 C 3-3-2005
222 B 3-3-2004
222 D 3-3-2005

tblDPASCap
[DPAS] [Capacity] [EFFECTIVE]
41663 16 1-1-2004
41663 12 1-1-2005
41663 10 1-1-2006
57891 46 1-1-2004
57891 120 1-1-2005
57891 80 1-1-2006
38806 15 1-1-2004

tblProgram_Details
[Program_ID] [DPAS_Code]
111 41663
111 57891
222 38806

So a Census Event that took place on 3-3-04 for Program_ID 111 would
have
a
total capacity of 62 (sum 16 and 46). If an event for Program_ID 111
took
place on 3-3-05 the capacity would come out to 132.

An Event on 3-3-04 or 3-3-05 for Program_ID 222 would have a capacity
of
15.

The tricky part of the formula is choosing the tblDPASCap record that
has
an
EFFECTIVE that has a range that the CensusDate fall in line with.

 

[Michel Walsh]

I think I see a little bit better what you want. With tables:


ProgramDetails
ProgramID DPAScode
111 41663
111 57891
222 38806





CensusEvent
ProgramID CensusDate
111 2004.03.03
111 2005.03.03
222 2004.03.03
222 2005.03.03





DPASCap
dpas capacity effective
41663 16 2004.01.01
41663 12 2005.01.01
41663 10 2006.01.01
57891 46 2004.01.01
57891 120 2005.01.01
57891 80 2006.01.01
38806 15 2004.01.01




My first action is to make one record giving the limit of validity for a
census, rather than having two records as it is right now:



SELECT CensusEvent.ProgramID,
Min(CensusEvent.CensusDate) AS Low,
Max(CensusEvent.CensusDate) AS High
FROM CensusEvent
GROUP BY CensusEvent.ProgramID;




QBetween
ProgramID Low High
111 2004.03.03 2005.03.03
222 2004.03.03 2005.03.03





Next, to find the limit to be involved from DPASCap:


SELECT QBetween.ProgramID,
Max(DPASCap.effective) AS LowEff,
Max(DPASCap_1.effective) AS HighEff
FROM ((QBetween INNER JOIN ProgramDetails
ON QBetween.ProgramID = ProgramDetails.ProgramID)
INNER JOIN DPASCap
ON ProgramDetails.DPAScode = DPASCap.dpas)
INNER JOIN DPASCap AS DPASCap_1
ON ProgramDetails.DPAScode = DPASCap_1.dpas
WHERE (((DPASCap.effective)<=[Low])
AND ((DPASCap_1.effective)<=[High]))
GROUP BY QBetween.ProgramID;


which returns:


Query1
ProgramID LowEff HighEff
111 2004.01.01 2005.01.01
222 2004.01.01 2004.01.01




And in the end, we are simply left with a sum:





SELECT Query1.ProgramID,
Sum(DPASCap.capacity) AS SumOfcapacity
FROM (Query1 INNER JOIN ProgramDetails
ON Query1.ProgramID = ProgramDetails.ProgramID)
INNER JOIN DPASCap
ON ProgramDetails.DPAScode = DPASCap.dpas
WHERE (((DPASCap.effective)
Between [LowEff] And [HighEff]))
GROUP BY Query1.ProgramID;






Query2
ProgramID SumOfcapacity
111 194
222 15






Is it what you were expecting?




Vanderghast, Access MVP

 

[knowshowrosegrows]

Wow, this is great. Let me mess with it and apply it and I'll get back to you.

Thanks so much

 

[knowshowrosegrows]

That is very cool - And very close.

For some reason, when I put it in with the accurate field/table names I got
only one record in "Query1' and 'Query2'.
Query1
ProgramID LowEff HighEff
111 2004.01.01 2005.01.01
Query2
ProgramID SumOfcapacity
111 194

The last issue is that the final query should tell me the SumOfCapacity that
was true for a given CensusDate

CensusDate ProgramID SumOfCapacity
2004.03.03 111 ???
2004.03.03 222 ???

Thanks

 

[Michel Walsh]

Do you use the same DATA as I did? or it is another set of DATA?


It also happened to me, first, but, in my case, that was because there were
typos in the DATA I typed in my tables.



Vanderghast, Access MVP

 

[knowshowrosegrows]

Yes, I used your data, but I will check my data.

 

[knowshowrosegrows]

OK, Yes I typo'd one piece of data. Phew!

But I still can't relate a SumOfCapacity to an exact CensusDate.

On CensusDate 2004.03.03 the SumOFCapacity for ProgramID 111 would be
16 plus 46 or 62. On 2005.03.03 the Sum for ProgramID 111 would be 12 plus
120 or 132.

I can't believe how much time you have put into figuring this out. I will
send you some virtual vegetables from my garden.

 

[Michel Walsh]

.... and, still for ProgramID 111, the grand total is thus 194, since 194 =
62 + 132, as you already figured out, and 194 is the result of the final
query associated to programID 111. Or you don't want the grand total? (That
should then be a matter to somehow add the date of DPASCap.effective in the
GROUP BY of the last query)




Vanderghast, Access MVP

 

[knowshowrosegrows]

I want to thank you for all your time on this.

I don't think I am explaining myself well.

I'll keep noodling with the queries you've given me to try and get the
product I'm looking for.

 

[Michel Walsh]

The modified query2, to become query3:


=============
SELECT Query1.ProgramID,
Sum(DPASCap.capacity) AS SumOfcapacity,
DPASCap.effective
FROM (Query1 INNER JOIN ProgramDetails
ON Query1.ProgramID = ProgramDetails.ProgramID)
INNER JOIN DPASCap
ON ProgramDetails.DPAScode = DPASCap.dpas
WHERE (((DPASCap.effective) Between [LowEff] And [HighEff]))
GROUP BY Query1.ProgramID, DPASCap.effective;
=============


returns the capacities, by 'dates' :



Query3
ProgramID SumOfcapacity effective
111 62 2004.01.01
111 132 2005.01.01
222 15 2004.01.01






Unfortunately, I don't see any newsgroup forum where we can post binaries,
so I cannot 'attach' the database I used.



Vanderghast, Access MVP

 

[knowshowrosegrows]

You're great to keep inflating this balloon.

The results you came up with shows me the effective date, I need a final
solution that shows the following.

CENUSDATE ProgramID SumOfCapacity

So I have to find the range in the Effective Dates that the CensusDate falls
into for each DPAS that makes up a ProgramID.

It is the Sum of the Capacities that were in effect during that date range
that I am looking for for each CensusDate/ProgramID record.