Running Query In Loop Using Incremental Date

[The Brown Growler]

Hi,

I hope to be able to run an append query repetitively with each occasion of running the query representing an increment of 1 day.

I have a date table containing the dates that I wish to use as incremental dates and I can join this table to my source data that contains the values for the append query.

However, I am stuck as to how to put it all together. I have provided an example below which I hope illustrates what I am trying to achieve:

1. DateTable - contains 1 field of dates that I wish to use as increments
[IncrementalDate]

2. SalesTable
[SalesDate]
[180 day sales value]
30 day sales value]
[10 day sales value]
[SalesStatus]: IIf([10 day sales value]>[30 days sales value] And [30 day sales value]>[180 day sales value],"Hot","Cold")

The result will be to append to my new table the [SalesStatus] value for each date of running the query.

I hope it is clear enough.

Thx for any help or advice or any alternative solutions to using queries such as some VBA or a macro etc.

Rgds
Growlos

 

[Scooterbug]

Storing calculations in a table shouldn't be done. The milestones you want to find can be calculated in a query based off of the sales date. No appending necessary.

180DayMark: SalesDate + 180
30DayMark: SalesDate + 30
10DayMark: SalesDate + 10

Any comparison can be done that way.

 

[The Brown Growler]

I wish to calculate the [SalesStatus] value each day for historic data, ie, from 1st Jan-08. If it was only today and moving forwards I would not need to run the repetitive calculations as I would run them each day from now on.

The loop is to run the calculations for each day going back to 01-Jan-08, perhaps I did not make that clear.

Thx & Rgds
Growlos

 

[The Brown Growler]

I have reposted this request in the Modules and VBA forum as I think I need to use a Do Loop and some VBA to achieve what I need.

Please consider this thread as closed. Alternatively, a moderator can remove this specific thread

Thx & Rgds
Growlos