Creating monthly records from effective date records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

karmaverma@gmail.com - 03 Feb 2006 23:30 GMT

I need help with this apparently simple problem. I have a table with
the following records:

Effective_Date Commodity Price
10/1/2005    0
5/1/2006    2750
10/1/2006    0

Now I need to generate the following monthly records using the above
table:

Date    Commodity Price
1/1/2006    0
2/1/2006    0
3/1/2006    0
4/1/2006    0
5/1/2006    2750
6/1/2006    2750
7/1/2006    2750
8/1/2006    2750
9/1/2006    2750
10/1/2006    0
11/1/2006    0
12/1/2006    0

I found a way to create this data but it is highly inefficient as it
involves 3 queries (nested). Since I am dealing with a significant
number of records, any ideas on how to get this done without multiple
queries will be highly appreciated.

Thanks,
Amit

Reply to this Message

CDMAPoster@FortuneJames.com - 05 Feb 2006 01:52 GMT

> I need help with this apparently simple problem. I have a table with
> the following records:
[quoted text clipped - 28 lines]
> Thanks,
> Amit

First try adding an auxiliary table (tblMonthsForCalculation) and
running the single query below to see if the speed problem goes away:

tblEffective
Effective_Date Date/Time Format m/d/yyyy
Commodity Price Currency

Effective_Date Commodity Price
10/1/05 $0.00
5/1/06 $2,750.00
10/1/06 $0.00

tblMonthsForCalculation
ID AutoNumber
theYear Long No Default
theMonth Long No Default

ID theYear theMonth
1 2005 10
2 2005 11
3 2005 12
4 2006 1
5 2006 2
6 2006 3
7 2006 4
8 2006 5
9 2006 6
10 2006 7
11 2006 8
12 2006 9
13 2006 10
14 2006 11
15 2006 12
16 2007 1

qryMonthlyPrice:
SELECT DateSerial(theYear,theMonth,1) AS theDate,
Last(tblEffective.[Commodity Price]) AS [Commodity Price] FROM
tblEffective, tblMonthsForCalculation WHERE
DateSerial([theYear],[theMonth],1)>=[Effective_Date] AND theYear = 2006
GROUP BY DateSerial(theYear,theMonth,1);

!qryMonthlyPrice:
theDate Commodity Price
1/1/06 $0.00
2/1/06 $0.00
3/1/06 $0.00
4/1/06 $0.00
5/1/06 $2,750.00
6/1/06 $2,750.00
7/1/06 $2,750.00
8/1/06 $2,750.00
9/1/06 $2,750.00
10/1/06 $0.00
11/1/06 $0.00
12/1/06 $0.00

The auxiliary table only needs to start at the last effective change
date prior to the year needed for the report. I couldn't tell from
your example so I am assuming that the change dates are always on the
first. Also, I did minimal testing. Note that GROUP BY seems to order
by date when a lone GROUP BY variable is a date. I don't think an
index on Effective_Date would hurt either.

James A. Fortune
CDMAPoster@FortuneJames.com

Reply to this Message

Bookreader - 06 Feb 2006 04:03 GMT

>I need help with this apparently simple problem. I have a table with
>the following records:
[quoted text clipped - 28 lines]
>Thanks,
>Amit

Instead of making the second table, you could just use something like
the following when you want to price something out.

SELECT price from table1 where priceDate < dteDate2 AND
priceDate > dteDate1

Or, use the same type of code in a loop to create the second table
using month-beginning or ending dates.

Also, in your tables Access won't like naming a column DATE, since
that is a reserved word. Much better to call it PriceDate or
something to make it clear what you mean.

Reply to this Message