How to produce a biweekly collection schedule 1

[stevefutcher]

My organisation provides a biweekly collection service for our customers, and I want to produce a calendar of collection dates. So far I have made a query that takes the collection code for the customer and produces a start date for the first collection in the next financial year.

What I want to do is to use this date to produce the next 25 dates, but the only way I can think to do this is by manually creating 25 new fields adding sequential multiples of 14.

Has anybody got any bright ideas?

 

[fneily]

So you want the 26 dates PRE-loaded in a table? This is a normalization discussion. I'd have a transaction table that looks like this:
CollectionCode Date OtherInfo
So, basically 26 RECORDS for a customer. The first two fields could be loaded by a little routine incrementing the first date by the multiple of 14.

Someone, I'm sure, will suggest that having 26 individual fields on ONE record won't violate normalization.

My arguments are thus:
You'd have repetitive column headings Date1, Date2, Date3, etc. - drop the number, so, Date, Date, Date. Violation of the first normal form.
Also, what if a customer drops service? Let's say they stop at Date15. Then that record is shorter then the rest. You now have variable length records which violates the first normal form.

In my table, that customer would just have 15 records and the table structure is maintained.

Research Normalization.
Unfortunately, I going away for the next three days and won't be able to debate any other poster's opinion.

 

[stevefutcher]

Thanks for the reply, let me elaborate.

Ideally I don't want to store the dates at all, I want to create a query to extrapolate the dates to make a calendar, then mail merge that with the customer address and send them a calendar.

I specifically don't want to have repetitive column headings but don't know how to avoid it. Could you give an example of a routine that increments the date?

Thanks

Steve

 

[dhookom]

I would store the dates at least temporarily. You can see how to create recurring dates in a sample download from

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ProgramError]

dates can be altered using the DateAdd() function in VBA
ie to increase the month by one month

take a look at..

http://msdn2.microsoft.com/en-us/library/aa262710(VS.60).aspx

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[stevefutcher]

I've writen this code

For i = 1 To Iterations

Set db = CurrentDb
strSQL = "INSERT INTO tblTempSchedule (MultiColln) VALUES (#" & CollDate & "#)"
db.Execute strSQL
MsgBox CollDate
CollDate = DateAdd("d", 14, CollDate)

Next

Which inserts the dates correctly except for the first iteration which is inserted as mm/dd/yyyy. All of the other iterations are inserted as dd/mm/yyyy which is what I want.

I added the message box, which always outputs as dd/mm/yyyy so I'm very confused.

Steve

 

[dhookom]

I would move the Set db out of the loop and format the date for the US format.

Set db = CurrentDb

For i = 1 To Iterations
    strSQL = "INSERT INTO tblTempSchedule (MultiColln) " & _
       "VALUES (#" &  Format(CollDate, "mm\/dd\/yyyy") & "#)"
    db.Execute strSQL
    MsgBox CollDate
   CollDate = DateAdd("d", 14, CollDate)
Next

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[stevefutcher]

Thanks, that seems to have sorted the problem

Steve

 

[dhookom]

The date formatting tip came from Allen Browne's page

http://www.allenbrowne.com/ser-36.html.

Duane MS Access MVP
Now help me support United Cerebral Palsy