Need Query Help

[knowshowrosegrows]

Hello

I have a table:
tblHistoricalCount

The fields are:
DPAS CODE
CAPACITY
EFFECTIVE_Date

Over many years, CAPACITY has changed on these programs and this table keeps
a running record:

DPAS CAPACITY EFFDate
111 25 3/1/2000
111 30 5/1/2005
111 21 2/1/2009

So, the capacity of 30 was only effective between 5/1/2005 and
1/31/2009
I have a second table:
tblCensusEvent

The fields are:
Census_ID
Census Date
DPAS Code
Census

I need a query that will tell me what the capacity was for a DPAS CODE on
the CensusDate that the Census_ID took place.

Census_ID DPAS CensusDate CAPACITY Census
399 111 6/19/2005 30 22
781 111 1/20/2009 21 15

Can anyone help me?

 

[Jeff Boyce]

It sounds like you are trying to find the correct tblHistoricalCount record
that corresponds to a tblCensusEvent record for the same [DPAS Code], where
the tblCensusEvent![Census Date] is greater than the
tblHistoricalCount![EFFECTIVE_Date]. In fact, it sounds like you want a
single record, so that would be the [Census Date] greater than the MAXIMUM
[EFFECTIVE_Date] (for the matching [DPAS Code].

Is this an accurate paraphrasing?

If so, one approach might be to find the maximum tblCensusEvent row matching
the given [DPAS Code] and [Census Date]. This might require a couple
"chained" queries, first deriving the maximum row, then using that to
determine the other values in tblCensusEvent.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[knowshowrosegrows]

Thanks for your response. I have been playing around with MAX but the trick
is that I might have a record with a CensusDate for a DPAS code that took
place ten years ago. The Capacity for that DPAS Code might have changed 6
times since then. Consequently, knowing the maximum Effective Date for the
DPAS Code doesnt correspond.

You are close-
I am trying to find the correct tblHistoricalCount record
that corresponds to a tblCensusEvent record for the same [DPAS Code], where
the tblCensusEvent![Census Date] is greater than OR EQUAL TO the
tblHistoricalCount![EFFECTIVE_Date]

AND LESS THAN ALL THE OTHER tblHistoricalCount![EFFECTIVE_Date]s FOR THAT
[DPAS Code].
--
Thanks

You all are teaching me so much

It sounds like you are trying to find the correct tblHistoricalCount record
that corresponds to a tblCensusEvent record for the same [DPAS Code], where
the tblCensusEvent![Census Date] is greater than the
tblHistoricalCount![EFFECTIVE_Date]. In fact, it sounds like you want a
single record, so that would be the [Census Date] greater than the MAXIMUM
[EFFECTIVE_Date] (for the matching [DPAS Code].

Is this an accurate paraphrasing?

If so, one approach might be to find the maximum tblCensusEvent row matching
the given [DPAS Code] and [Census Date]. This might require a couple
"chained" queries, first deriving the maximum row, then using that to
determine the other values in tblCensusEvent.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

Try This:

SELECT Census_ID, [DPAS Code], [Census Date],
(SELECT Capacity
FROM tblHistoricalCount AS HC1
WHERE HC1.[DPAS Code] = tblCensusEvent.[DPAS Code]
AND Effective_Date =
(SELECT MAX(Effective_Date)
FROM tblHistoricalCount AS HC2
WHERE HC2.[DPAS Code] = HC1.[DPAS Code]
AND HC2.Effective_Date <= tblCensusEvent.[Census Date]))
AS Capacity, Census
FROM tblCensusEvent;

Ken Sheridan
Stafford, England

 

[knowshowrosegrows]

Thanks for your great reply.

I am having trouble undertsanding your code because I gave you the same name
for
tblHistoricalCount.DPAS CODE and
tblCensusEvent.DPAS Code.

Would you give me back your formula replacing

tblCensusEvent.Prm_Code for tblCensusEvent.Dpas Code?

You are the best. Thanks


--
Thanks

You all are teaching me so much

Try This:

SELECT Census_ID, [DPAS Code], [Census Date],
(SELECT Capacity
FROM tblHistoricalCount AS HC1
WHERE HC1.[DPAS Code] = tblCensusEvent.[DPAS Code]
AND Effective_Date =
(SELECT MAX(Effective_Date)
FROM tblHistoricalCount AS HC2
WHERE HC2.[DPAS Code] = HC1.[DPAS Code]
AND HC2.Effective_Date <= tblCensusEvent.[Census Date]))
AS Capacity, Census
FROM tblCensusEvent;

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

This should do the trick:

SELECT Census_ID, Prm_Code, [Census Date],
(SELECT Capacity
FROM tblHistoricalCount AS HC1
WHERE HC1.[DPAS Code] = tblCensusEvent.Prm_Code
AND Effective_Date =
(SELECT MAX(Effective_Date)
FROM tblHistoricalCount AS HC2
WHERE HC2.[DPAS Code] = HC1.[DPAS Code]
AND HC2.Effective_Date <= tblCensusEvent.[Census Date]))
AS Capacity, Census
FROM tblCensusEvent;

To give you an idea of how it works, the outer query, as well as returning
the Census_ID, Prm_Code, [Census Date] and Census columns in the usual way
also includes the first subquery in its SELECT clause, returning the Capacity
from the tblHistoricalCount table, which is given an alias of HC1 to
distinguish it from the instance of the same table in the second subquery
which has an alias of HC2. The first subquery is correlated with the outer
query on the [DPAS Code] and Prm_Code columns to restrict it to rows which
match the outer query's current row's Prm_Code.

To get the first subquery to return the appropriate capacity its is
restricted by including the second subquery in its WHERE clause. The second
subquery is restricted to the row with the same [DPAS Code] as the first
subquery's current row (which also means the same as the outer query's
current Prm_Code of course) and which has the latest (MAX) Effective_Date
which is less than or equal to the outer query's current row's [Census Date],
so the capacity returned by the first subquery should be that which is in
effect for the relevant census date.

Ken Sheridan
Stafford, England

Thanks for your great reply.

I am having trouble undertsanding your code because I gave you the same name
for
tblHistoricalCount.DPAS CODE and
tblCensusEvent.DPAS Code.

Would you give me back your formula replacing

tblCensusEvent.Prm_Code for tblCensusEvent.Dpas Code?

You are the best. Thanks


--
Thanks

You all are teaching me so much

Try This:

SELECT Census_ID, [DPAS Code], [Census Date],
(SELECT Capacity
FROM tblHistoricalCount AS HC1
WHERE HC1.[DPAS Code] = tblCensusEvent.[DPAS Code]
AND Effective_Date =
(SELECT MAX(Effective_Date)
FROM tblHistoricalCount AS HC2
WHERE HC2.[DPAS Code] = HC1.[DPAS Code]
AND HC2.Effective_Date <= tblCensusEvent.[Census Date]))
AS Capacity, Census
FROM tblCensusEvent;

Ken Sheridan
Stafford, England

Hello

I have a table:
tblHistoricalCount

The fields are:
DPAS CODE
CAPACITY
EFFECTIVE_Date

Over many years, CAPACITY has changed on these programs and this table keeps
a running record:

DPAS CAPACITY EFFDate
111 25 3/1/2000
111 30 5/1/2005
111 21 2/1/2009

So, the capacity of 30 was only effective between 5/1/2005 and
1/31/2009
I have a second table:
tblCensusEvent

The fields are:
Census_ID
Census Date
DPAS Code
Census

I need a query that will tell me what the capacity was for a DPAS CODE on
the CensusDate that the Census_ID took place.

Census_ID DPAS CensusDate CAPACITY Census
399 111 6/19/2005 30 22
781 111 1/20/2009 21 15

Can anyone help me?

 

[knowshowrosegrows]

You are incredible

You can't know the difference this makes.

With this missive I am forwarding you a certificate good for a three year
supply of Basil, Tomatoes and Sugar Snap Peas from my garden.
--
Thanks

You all are teaching me so much

This should do the trick:

SELECT Census_ID, Prm_Code, [Census Date],
(SELECT Capacity
FROM tblHistoricalCount AS HC1
WHERE HC1.[DPAS Code] = tblCensusEvent.Prm_Code
AND Effective_Date =
(SELECT MAX(Effective_Date)
FROM tblHistoricalCount AS HC2
WHERE HC2.[DPAS Code] = HC1.[DPAS Code]
AND HC2.Effective_Date <= tblCensusEvent.[Census Date]))
AS Capacity, Census
FROM tblCensusEvent;

To give you an idea of how it works, the outer query, as well as returning
the Census_ID, Prm_Code, [Census Date] and Census columns in the usual way
also includes the first subquery in its SELECT clause, returning the Capacity
from the tblHistoricalCount table, which is given an alias of HC1 to
distinguish it from the instance of the same table in the second subquery
which has an alias of HC2. The first subquery is correlated with the outer
query on the [DPAS Code] and Prm_Code columns to restrict it to rows which
match the outer query's current row's Prm_Code.

To get the first subquery to return the appropriate capacity its is
restricted by including the second subquery in its WHERE clause. The second
subquery is restricted to the row with the same [DPAS Code] as the first
subquery's current row (which also means the same as the outer query's
current Prm_Code of course) and which has the latest (MAX) Effective_Date
which is less than or equal to the outer query's current row's [Census Date],
so the capacity returned by the first subquery should be that which is in
effect for the relevant census date.

Ken Sheridan
Stafford, England

Thanks for your great reply.

I am having trouble undertsanding your code because I gave you the same name
for
tblHistoricalCount.DPAS CODE and
tblCensusEvent.DPAS Code.

Would you give me back your formula replacing

tblCensusEvent.Prm_Code for tblCensusEvent.Dpas Code?

You are the best. Thanks


--
Thanks

You all are teaching me so much

Try This:

SELECT Census_ID, [DPAS Code], [Census Date],
(SELECT Capacity
FROM tblHistoricalCount AS HC1
WHERE HC1.[DPAS Code] = tblCensusEvent.[DPAS Code]
AND Effective_Date =
(SELECT MAX(Effective_Date)
FROM tblHistoricalCount AS HC2
WHERE HC2.[DPAS Code] = HC1.[DPAS Code]
AND HC2.Effective_Date <= tblCensusEvent.[Census Date]))
AS Capacity, Census
FROM tblCensusEvent;

Ken Sheridan
Stafford, England

:

Hello

I have a table:
tblHistoricalCount

The fields are:
DPAS CODE
CAPACITY
EFFECTIVE_Date

Over many years, CAPACITY has changed on these programs and this table keeps
a running record:

DPAS CAPACITY EFFDate
111 25 3/1/2000
111 30 5/1/2005
111 21 2/1/2009

So, the capacity of 30 was only effective between 5/1/2005 and
1/31/2009
I have a second table:
tblCensusEvent

The fields are:
Census_ID
Census Date
DPAS Code
Census

I need a query that will tell me what the capacity was for a DPAS CODE on
the CensusDate that the Census_ID took place.

Census_ID DPAS CensusDate CAPACITY Census
399 111 6/19/2005 30 22
781 111 1/20/2009 21 15

Can anyone help me?