Grouping cost by periods (1 Viewer)

[Ste4en]

I have 2 tables one with costs by date e.g.

Ref1; Amount; Date

The other table has accounting periods:

period1; from:12/30/02; to 01/26/02
period2; from: 01/27/02; to 02/23/02 etc


Where or how do I link this data to do this.

Thanks

Steve

 

[Robert Dunstan]

You'll need to add to both tables a field called say PeriodID and then link the 2 tables on this field

 

[Ste4en]

OK, I can add period ID to the cost data table, but how to I get the field to fill - i.e. if date is between fromdate and todate = period x OR if date is between ........



Thanks

 

[Robert Dunstan]

Steve,

I'm not sure but you may need to run an update query, unfortunately I can't help you much on this one but hopefully someone else will.

I have attached an example which shows the relationships and I've created a simple crosstab query as well to show you how you can display the data.

HTH & good luck

Rob

 

[Ste4en]

Thanks for helping. I think the update query may be a way of accomplishing this. I use update queries for other things but still stumpt on how to assign the period number depending on the date. IF it were just a few periods a nested if would work:

if entrydate is between dateA and DateB then period =1, if entrydate is between dateC and dateD then period = 2 etc etc but it will be a mile long abnd surely is not the right way to do it.

Any ideas anyone,
thanks

 

[Fornatian]

Build a custom function to return an integer based on the date you pass in as a parameter suchas this roughly:

Public Function PeriodID(TheDate as Date) as Byte

Select Case TheDate
Case >=#01/01/01#

PeriodID = 1

Case >=#01/02/01#

PeriodID = 2

Case >= AnotherValue

PeriodID = TheNextValue

End Select

Then use this as the criteria for the update field passing in the date.

Update Field: PeriodID([MyDateField])

 

[Ste4en]

Thanks, but does this way mean I will have 100 Cases is I have a 100 cost periods?

 

[Fornatian]

Are your date periods evenly spaced?

 

[Ste4en]

No, they are 4 or 5 week months.
e.g Dec 29 to jan 25
jan 26 to feb 22
mar 23 to mar 29

Thanks

 

[Rich]

Why not add the cost period to the table containing the dates?

 

[Ste4en]

Yes, That is exactly what I want to do - however I have 175,000 records so need to do an update query or have the report query assign the period as the query processes - either way I dont know how.

Any ideas,

 

[Fornatian]

If I were you I would create an excel list with a column of dates and a column of relative periodID's. Link this excel sheet into your database, include it in an update query, linking the date field from your table to the datefield in the linked excel sheet which will then return the periodID you require. Use this value as the update value for a new field et voila, your periodID is in the table for grouping.

Repeat the above for as many dates as required(if more than the height of an excel sheet.

You can always delete the excel sheet after if you don't need it!

If you don't like that idea is there anything consistent about how the periods are identified? A formula per say?

 

[Ste4en]

Thanks I think that will work - it will get me out my current crunch but I don't think it is the right way or a permanent solution?

There is no formula to the periods - the object is to make 12 full week months i.e. either 4 or 5 weeks.

Many thanks

 

[neileg]

Yeah, we have 4 and 5 week months too. You have to have a table that defines period numbers and dates. Not sure why Fornatian wants this as an Excel table. This could just as easily be in your db. Yes you have to have a big table if you have lots of periods.

 

[Fornatian]

I consider it easier to create a cascading column of continuing dates in excel by using the A2+1 formula to create date column and pulling it down for my range.

Also by putting an "Increment" column in the data set you use an "X" to indicate to increment the PeriodID using a formula like:

In Cell B2 would number 1.
In Cell B3 would be a formula like this:

=Sum(B2+If(C3="X",1,0))

Pull this down your dataset and it will increase the periodID by 1 to reflect a different period.

Ste4en, how do you know if a period is a 4 or 5 week period? From the dates you've given I think your periods always end on the Last Satuday of the month, and the day after is the Start of the NewPeriod, is this right?(rubs head with dreaded confusion )

 

[Ste4en]

I used Excel cause its easier to get a column of dates and then to add the period. I did that for 5 years in a few minutes, then imported the table into Access. A query brings the cost and period data together. Our periods end on the last Sat of each month.

It works fine - except.........................
One thing I need to do is a cross tab of cost codes (rows) and dates (column). I started with the column headers as TIC Jan 2002, TIC Feb 2002 etc, but this did not sort well, nor did Jan 2002 - TIC, Feb 2002 - TIC. So tblperiod ended up with DataDate and Period, where Period is the first day of the month, e.g TIC Jan 2002 = 01/01/2002. However when I use this in the cross tab and ask it to sort column assending by
ColHeader: Format([Period],"mmm-yy") it sorts alphabetically.

Any ideas.

Thanks all for helping.
Ste4e

 

[Fornatian]

Ste4en,

if you can confirm that your periods alway start on the Last Sunday of the month, I may be able to offer you a graceful recyclable solution.

 

[Ste4en]

No they do not. The period ends on the last Sat, so the next period starts on the Sunday but that is not always the last Sunday in the month.....actually for this year the period does start on the last Sunday. Last year however September and December both started on the first Sunday of the month.


If I do not format the [period] to ColHeader: Format([Period],"mmm-yy") , and leave it as 01/01/2002; 02/01/2002.... etc they sort fine. How else can I format this field to retain it with date properties.

Thanks

 

[Fornatian]

Can't you include the periodID in twice, one formatted, the other not formatted but used to sort the data?

Again, is it 'cast iron' that the periods always end on the last Saturday of the month and a new one begins the day after?

 

[Ste4en]

Yes its cast iron. Always the last Saturday of the month. The first day is the dat after.

Using periodID twice would work but where do I put it - in a cross tab. SQL is as follows:

TRANSFORM Sum([qryDataPreparation].[JCAmount]) AS SumOfJCAmount
SELECT [qryDataPreparation].[JCBudgetCode], Sum([qryDataPreparation].[JCAmount]) AS [Total Of JCAmount]
FROM qryDataPreparation
GROUP BY [qryDataPreparation].[JCBudgetCode]
ORDER BY Format([period],"mmm\,yyyy")
PIVOT Format([period],"mmm\,yyyy");

I tried ORDER BY [period] but I get "you tried run a query that does not use the specified expression [period" as part of an aggregate function.

Thanks again.