How to determine when somthing should happen.

[Guest]

I need to find out when to do somthing based on a schedule. Here is the
problem for some of my customer schedule is Monday, Wednesday and Friday.
Other customers Tuesday and Friday. Yet others may be only on the 10th of
the month or 3 times a month like 5th, 15th and 25th. Now come the problem.
If the customer request needs to fall on Monday and it is a hoilday then I
need fill that request on Friday. If the request is on the 15th and that is
a Monday hoilday I need to fill the request on Friday the 12th and it the
15th is on Sunday then I would also need to fill the request on Friday the
13th. I may be able to change my process fill the request the day after the
hoilday or weekend.

 

[Tom Ellison]

Dear Lee:

This is not going to be extremely simple, but it is eminently doable.

I believe you are going to need a calendar table, one with a row for every
date worth considering.

1/1/2006
1/2/2006
1/3/2006

and so on.

Use the DatePart function:

DatePart("w", 3/11/2006)

The above returns 7 meaning today is a Saturday (the 7th day of the week).
You may want to record the days in the schedule using 1 (Sunday) through 7
(Saturday) to make this easier to implement.

In the table of dates, record whether a day is a "holiday", perhaps by
recording the name of the holiday in a second column. Leave this column
NULL for all days that are not holidays.

Now, to match a request with the calendar, write a query that first returns
all dates >= to the request date. Filter out all the dates that are closed
due to holiday or weekends. Filter out dates that are not on the customer's
schedule. Now you have a list of dates that remain. The query could choose
only the earliest of these dates, or even choose more than one.

To give you any kind of specifics, I would need all the information on what
you have designed so far. How far has your design proceeded?

Ordinarily, I would look at the needs of a mechanism like this before
designing the tables on which it is to function. Trying to get a complex
function out of a design that is already "completed" can use up a lot of
unnecessary time and effort.

Tom Ellison

 

[Guest]

Hello Tom,

Thank you, I had thought of using a calendar table, but this would be
something my end-users would need to maintain from year to year.

So here is the deal. I’m creating a application for a friend of mine that
owns a company that receives mail for his customers and then re-mail it to
there current location.

At current there are about 14 options (MailingFrequency) listed below .
10th only
1st & 15th
5th & 20th
Daily
2X Week T&F
3X Week M-W-F
Monday
Tuesday
Wednesday
Thursday
Friday
One Time Only (Specific Date)
Hold Until Further Notice
Customer Pick-up

They would like to be able to allow the customer to also pick-up to as many
as there days a month to ship, customer choice on the days.

So now I have a table to track customer require that looks like the following.
OrderID
AccountNumber
Address1
Address2
City
StateOrProvince
PostalCode
Country
StartDate
EndDate
MailingFrequency (listed above)
Notes

I hope this makes sense.

I could see where creating a calendar table would work, but woulded I need
to create a row for every MailingFrequency for that day?

I was thinking it would need to look like this. Since they are closed
Thankgiving and the Friday after, I thought it would need a column table that
looks like this.

11-22-2006 2X Week T&F
11-22-2006 3X Week M-W-F
11-22-2006 Daily
11-22-2006 Wednesday
11-22-2006 Thursday
11-22-2006 Friday

The way they do it now is to pick each day (11-22, 11-23, 11-23 etc...). I
could do this but I was hoping to automate the process a little.

If you have any ideas let me know.

Thanks
Lee

 

[Tom Ellison]

Dear Lee:

I was thinking of a date table:

11/22/2006
11/23/2006 Thanksgiving
11/24/2006
11/25/2006

The only thing recorded other than dates is holidays.

You can obtain the days of the week using DatePart().

You could create a table that expresses the various "schedules" so you can
name the schedules:

rule func param
10th only day 10
1st & 15th day 1
1st & 15th day 15
Daily all
2X Week T & F WDay Tuesday
2X Week T & F WDay Friday

You would write a function operating on the date from the calendar and the
rule. The function would look up the rule and parameter for that function
and check whether the date meets the requirements. The function would
return boolean (true/false) whether the date meets any of the rules.

For "One Time Only" I have no idea where you will have stored the Specific
Date.

Daily would just always be true (including Sundays?)

Hold and Customer Pick-up would always be false.

This should work very well in a query. The query asks the function when the
mail should be processed. The query returns all the appropriate dates >=
Today(). You can take the minimum.

Tom Ellison

 

[Guest]

Hello Tom,

Thanks again, this would be a VBA function?

Thanks
Lee

 

[Tom Ellison]

Dear Lee:

When I said "function" yes, I did mean VBA. That's the only kind of
function of which I am aware in Access. Well, there are UDFs in MSDE which
is included in Access, but I didn't mean those.

Tom Ellison