Summing Consecutive Records

[Spidey]

Hello all,

I have a small problem I am hoping people can help me out with. Right now, I am converting a spreadheet to Access, and am having trouble mimicing some functionality, and getting similar speed.

Given:

Date Sales
------------------------------
10/30/2002 50
10/31/2002 60
11/1/2002 55
11/4/2002 45
11/5/2002 55

Given an integer variable input on a form (lets say X), for each date, I want the sum Sales for the the next X business days. Sounds easy off the bat, but I need to worry about weekends, so I cannot compare raw dates. I need to determine what the date X days from the start would be (accounting for weekends).

Like i said, I was able to get this to work, but it runs a little too slow for something that may be changed freqently.

Here is the Query that gets the data:

SELECT		tR001A.dDate,
			tR001A.Sales,
			Sum(NextDay.Sales) AS SumOfSales
FROM		tR001A,
			tR001A AS NextDay
WHERE		AddWorkDays(1,[tR001A].[dDate])<=[NextDay].[dDate]
	AND	AddWorkDays(X,[tR001A].[dDate])>=[NextDay].[dDate]
GROUP BY		tR001A.dDate,
			tR001A.Sales

'AddWorkDays' is a function that I wrote. I realize that UDFs are slow, but I was getting the same response time with using IIF's in the Criteria.

If I replace the 'AddWorkDays' function with simple addition (i.e. [tR001A].[dDate] + 1 <= [NextDay].[dDate]), it is much faster, but again, that does not take ignore weekends.

So to bring it all around, is it possible to look at one record, and sum the next X in the table?

Thanks in advance!

Jonathan

 

[Gates Is Antichrist]

Here is a UDF approach, but I can't visualize another route Start with the Weekday function or a close variant. Make it 0-based. Now if you let f be the original day of week (symmetric to your DOW weekday function), define something like
TotalDays=X+2*int( 1+(X+f-7)/5)

This should give the true date span days, where it adds 2 days for each weekend in the timespan.

This is untested for endpoints and may need a plus or minus 1, but essentially does what it sounds like you want. Note: I assumed 0=Saturday in a quick test; you may want to tweak your DOW function to do that too.

This would give you an ending date to do the X day span, and I assume you know where to go next.

Due to time I just rushed this response; if you want need a more detailed explanation just holler.

 

[Ian Mac]

Without having Access installed right now you should be able to look at Weekday() as a criteria and IIF.

Hope that helps, I'll put Access back on in the morning and look at this more.

 

[mdmilner]

With large amounts of fragmented data, indexing may become very important to performance. Would be like Night & Day in fact - something that runs in 2-3 seconds on an indexed database could take many minutes.