Query to Calculate Dates Based on First Date 1

[blumbra]

Hello,

Here is what I would like to do and I'm stumped.

I have a product that I sell that gets serviced every 6 months for 3 years. So if they purchased the product on 06/01/05 then the service dates would be:

12/01/06
06/01/06
12/01/07
06/01/07
12/01/08
06/01/08

I have an order that I put in that has the purchase date and a checkbox that tells me if they purchased the extended service.

I "simply" need a query that can take the start date and generate the (6) service call dates placing them into a table of "Service Calls". The biggest problem I'm having is how do I generate the recurring dates. I know I will be using the dateadd function etc. It will be more complicated in the end of course but I just don't know how to get those (6) dates based on the first date to go into a table.

Any ideas?

-Bryan

 

[bboffin]

Assume ServiceCall table is defined as:
ServiceCallID AutoNum
OrderID Long Integer
Date1 DateTime
Date2 DateTime
Date3 DateTime
Date4 DateTime
Date5 DateTime
Date6 DateTime

This simplifies the problem by avoiding the need to insert more than one record per order.

Then try something like

INSERT INTO ServiceCall (OrderID, Date1, Date2, Date3, Date4, Date5, Date6) 
SELECT Order.OrderID, 
DateAdd("m", 6, Order.PurchaseDate), 
DateAdd("m", 12, Order.PurchaseDate), 
DateAdd("m", 18, Order.PurchaseDate), 
DateAdd("m", 24, Order.PurchaseDate), 
DateAdd("m", 30, Order.PurchaseDate), 
DateAdd("m", 36, Order.PurchaseDate)
FROM Order WHERE Order.ExtendedService=-1
[code]
This is just off the top of my head and will probably require a tweak or two to get working.



Bob Boffin

 

[blumbra]

Ok, I see how that works. I had not thought about just having all the dates in one record and at first I thought this would work for me. However, this doesn't allow me to mark when the service call was done, whether they were contaced for the call. In essence there is information for each call that I need to hold.

I think I can just modify this to do the exact same thing except run it through a while loop 6 times?

What is the syntax for a while loop in Access?

-Bryan

 

[bboffin]

You can't write a loop in Access SQL. The only way you can do it is to write a VB module that would read the records from the Order table and create the records that way.

You could I suppose achieve something like the same effect by using 6 separate Insert Queries each one inserting one of the ServiceCall dates. This could be done in a simple macro.

As an alternative you could extend the ServiceCall table to include columns called:

DateCompleted1 datetime
Contacted1 Yes/No
DateCompleted2 datetime
Contacted2 Yes/No
DateCompleted3 datetime
Contacted3 Yes/No
DateCompleted4 datetime
Contacted4 Yes/No
DateCompleted5 datetime
Contacted5 Yes/No
DateCompleted6 datetime
Contacted6 Yes/No

This isn't particularly good database design practice but is the only other easy way to do it.

You could also consider switching over to use MSDE as your database in which case you can use a Stored Procedure. Transaction SQL (the dialect used by MSDE) does support looping and allows multiple insertions in a single Stored Procedure.




Bob Boffin

 

[dhookom]

I highly recommend that you don't create a spreadsheet from this. Each service date should create a new date in a related table. There is absolutely no way that I would create a solution that wouldn't easily allow for 3 dates, 6 dates, 10 dates, etc.

There is a recurring schedule demo at

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

Another methdo is to create a simple table of numbers (tblNums) with a single, numeric field (Num) with values 1 through 100 or whatever. You could use this table in an append query to create service dates. Set the servicedate expression to something like:
ServiceDateateAdd("m",6*[Num], [PurchaseDate])
Also append the primary key field from you purchase record.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[blumbra]

Thank you both for you suggestions.

The quick and dirty way would be to have the 6 fields with dates and yes/no complete. I could do this easily but I do like to be a little more dynamic than that. What if I setup a 4 year plan?

I had already looked at your recurring schedule Duane before posting (yes I did my tek-tips searches!)

Quite frankly I just got confused. I'm a shade tree programmer so it takes me a while to digest things.

Duane, I tried your second suggestion with SUCCESS! I had to chew on it for a while to see what you were saying to do but I got it. Correct me if I'm wrong but wouldn't it be just as possible to create tblNums on the fly for the number of services calls needed and then delete it when the calculation is finished?

(Not asking for scripting help on that, just throwing out the idea for the next guy that does the forum search)

Thanks for all your help.

-Bryan

 

[dhookom]

Congrats on following my somewhat rambling solution. I would not create and delete a table on the fly. You never know when a table of numbers might come in handy. For instance, if you have a label that you want to print X number of times based on a Qty field in a table.

Creating and deleting tables can cause bloat.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[blumbra]

Actually, I was being stupid and thinking that I needed the exact amount of numbers in that tblNums. I wasn't thinking of using the criteria <=6 etc.

Having a static table also makes it easier for multiple users. I could see a mess of sorts if two people were trying to delete/update that table at the same time.

Again, thanks for your help. Its working great.

-Bryan