Need help with: DayCounts in Access 2003

[eskelies]

I have a rolling cumulative income amount for each day. Right now my query would need me to run it everyday (weekday, weekend or holiday) for it to work. My data looks something like this:

Distribution dollar amounts:

Today (T)      T+1       T+2
300              350        375

These distribution dollar amounts are for Today (T). In my query, I have a cumulative total for the entire month, which I enter into a different system.

For example if the cumulative distribution is based on two days above we would have the following:

ie. 650 (9/26/07) would actually be inputted on 9/27/07 period.

Now my issue arises over weekends and holidays. I have performed a day count, which allows the query to tell me the exact number of days since the last update.

What I would like to do with that is calculate the cumulative total over the weekend as if I was running it on business day.

Below is my query with what I have so far. Any help would be greatly appreciated.

SELECT [Units Entries (GLDST-)].[Fund Number], Max([Units Entries (GLDST-)].[Compass Entry Date]) AS [MaxOfCompass Entry Date], Last([NAV's - ETF Income Adjustments (data)].UNITS) AS LastOfUNITS, Last(DateDiff("d",[Compass Entry Date],Date())) AS Daycount
FROM [Units Entries (GLDST-)] INNER JOIN [NAV's - ETF Income Adjustments (data)] ON ([Units Entries (GLDST-)].[Compass Entry Date] = [NAV's - ETF Income Adjustments (data)].Date) AND ([Units Entries (GLDST-)].[Fund Number] = [NAV's - ETF Income Adjustments (data)].[Fund Number])
GROUP BY [Units Entries (GLDST-)].[Fund Number];

[eskelies]

Here is the situation: Access 2003

I am forecasting future dollar amounts, which are cumulative each and every day of the month. What I have done so far (which isn't much) is built a datasheet called, "Calendar." Calendar gives me all the days out to 2014 and with a "Yes" tells me if it is a holiday/weekend.

Firstly, is there a way to do this without building this from scratch? Such as using a count function? Help on this would be greatly appreciative, but not necessary.


Secondly, how can I go about taking my calendar's information and the hardcoated information, which is business day amounts and forecast them for the weekend.

For example, on Thursday we have a cumulative dollar amount of $200. On friday we have a daily dollar amount of $100. This $100 dollars will be added to the $200 (Giving us $300) for Saturday's (T+1) cumulative dollar amount. The same goes for Sunday (T+2), Mondays (T+3) and Tuesday (T+4) if monday is a holiday.

Thank you very much, for assistance. I grealty appreciate it.

[nico5038]

You might try the code from this link:
http://www.tek-tips.com/faqs.cfm?fid=3213

It uses a number of days and a startdate, but could be used to add your table with amounts and calculate the needed sum.

Nic;o)

[NeoPa]

Eskelies,

If you're still interested in this could you try to ask it in a way that makes more sense. I tried to follow what you were saying but there were too many references to entirely unexplained concepts. I couldn't even work out the gist of what you're working with.