Comparing same days of different years

[danikuper]

Hi, I've searched the forum but couldn't find an answer for this one... if it has already been answered I apologize.

I have a table with data for 2002, 2003 and 2004. I need to compare weekly data but we know that week1 in 2002 will not have the same number of days as week1 in 2003 or 2004. I need to be able to compare every 7 days of each year... so when week1 2002 is compared with week1 in 2003 and 2004 they will all have the same durations.

What is the best way to do that?

Let me know if you need clarification. Thanks in advance for the help!

P.S. using Access 2000.
daniel

 

[dcx693]

You could compare Jan 1 to Jan 7 of each year, etc... Is that what you want? You can use the DateSerial function to get at those dates like this:

Between DateSerial(2002,1,1) And DateSerial(2002,1,7)

Ultimately though, the technique you use will depend on how you want to view/analyze the results. For example, you can use the Partition() function to slice each year up into 7 day pieces and display it in a crosstab query.

 

[danikuper]

Humm... it seems that the Partition() Function is what I'm looking for. What would the syntax look like? My table looks like this:

Invoice
Date
Amount
Customer
..etc..

The [Date] field has the date I want to compare while the [Amount] has the $ value for comparison.

Partition([Date].... )? something like that I believe. How do you specify the 7 days grouping?

Thanks!
daniel

 

[dcx693]

I suggest you take a look at the Access online help. This is an excerpt from the example:

This example assumes you have an Orders table that contains a Freight field. It creates a select procedure that counts the number of orders for which freight cost falls into each of several ranges. The Partition function is used first to establish these ranges, then the SQL Count function counts the number of orders in each range. In this example, the arguments to the Partition function are start = 0, stop = 500, interval = 50. The first range would therefore be 0:49, and so on up to 500.

SELECT DISTINCTROW Partition([freight],0, 500, 50) AS Range,
Count(Orders.Freight) AS Count
FROM Orders
GROUP BY Partition([freight],0,500,50);

This thread: Age bracket query has another example of using the Partition function.

 

[danikuper]

Yeah!

The partition function seems to be my savior.

thanks a lot

daniel