VBA To Run Do Loop Using Date List & Append Query

[The Brown Growler]

Hi,

I would be grateful if anyone could assist me by providing some code to run a Do Loop that carries out the following:

1. Pass a date from a list of dates to a text box on a form.
2. Run an append query in which the query uses the date set in the text box in the form.
3. Repeat the process for all dates in my list of dates

The objects I have for the task are:

Table named [T_Dates] containing one field named [SalesDate] which is my list of dates for the loop.

Form named [F_Date] containing one text box named [Text0] which is the recipient field for the date used in the loop

Query named [Q_Append_SS] which runs via the loop and appends the result to another table in the database. It is this append query which references the date stored in the text box [Text0] in the form [F_Date]


I hope the above is explained well enough to understand. My thoughts were to either have some code that could be assigned to the OnClick event property of a button or a specific module that I can run from the modules tab in the database.

However, I would be grateful for any suggestions or solutions that will assist with the above.


Thx & Rgds
Growlos

 

[Galaxiom]

I expect you are trying to produce a report for totals sales on each of the days in question or something like that.

This task can be done entirely in a query without VBA code. The query would use the aggregate (Totals) capability in Access.

However you would not normally write these results to a table because this would mean you were storing derived information. The query would be run whenever you wanted the report.

Post more about the details the data you were intending to place in the other table and the tables that it is derived from.

 

[The Brown Growler]

Galaxiom,

I am trying to produce a table with 3 sales figures:

Total Sales in last 180 days per customer (calculated daily)
Total Sales in last 30 days per customer (calculated daily)
Total Sales in last 10 days per customer (calculated daily)

However, I want the daily values for these totals for every day going back to 01-Jan-08. Each day these total sales figures will evolve and I wish to track this evolution.

In addition, depending on the values of sales for 180, 30 and 10 days there is a SalesStatus flag to be assigned on a daily basis to each customer for each of the days going back to 01-Jan-08, ie,

SalesStatus: IIf([10DS]>[30DS] And [30DS]>[180DS], "Hot","Cold")

I hope that the above clarifies what I am trying to do.


Thx & Rgds
Growlos

 

[Galaxiom]

Create a query with [T_Dates] and the Invoice table. No joins.

Add [T_Dates].[SalesDate] and CustomerID, InvoiceValue, InvoiceDate from the invoice table. Uncheck Show on the InvoiceDate field.

Turn on the Totals in the query.
Set the Total of InvoiceValue to Sum and the others to GroupBy.
Set the criteria for InvoiceDate to > [T_Dates].[SalesDate] -10
This will give you the total of sales by customerID in the ten days up to each date in [T_Sales].

Copy the query for the other periods and change the criteria as required.

Make another query based on the first three queries joined on CustomerID and [SalesDate]. Set three fields to show the Totals results from those queries.

Derive the last field using your IIF expression.

 

[The Brown Growler]

Galaxiom,

Many thanks, it worked perfectly, most grateful for your kind help.

Update: Spoke to soon, there seems to be an issue with the figures and it will not run on larger data sets. I tested it on 30 days worth of dates, however it fails to complete on the full 674 days data set.


Thx & Rgds
Growlos

 

[The Brown Growler]

Galaxiom,

My reply was slightly premature. I think I have an issue with the query result. I tested the 180 day query for the month of July 2009, ie, the 31 days in July 2009 were the dates in my [T_Dates] table.

On running the query and checking for a customer it seems that the query is getting the sales amounts for 180 days, 179 days, 178 days on 01-Jul-09, 02-Jul-09, 03-Jul-09 respectively. Rather than 180 days, 180 days, 180 days on 01-Jul-09, 02-Jul-09, 03-Jul-09.

I have come to this conclusion as the 180 day sales amounts per day in July are all decreasing as the month progresses for all of the customers. I have pasted a sample below which illustrates it:

Customer180 Day SalesAmtSalesDateA J M130601-Jul-09A J M130002-Jul-09A J M130003-Jul-09A J M130004-Jul-09A J M129205-Jul-09A J M128506-Jul-09A J M127907-Jul-09A J M127308-Jul-09A J M126209-Jul-09A J M126210-Jul-09A J M126211-Jul-09A J M125312-Jul-09A J M124413-Jul-09A J M123914-Jul-09A J M123215-Jul-09

As this trend is occurring for all customers in the 180 day sales amounts either I have set up the query wrongly or there is possibly something extra required in the query ?

Damn, thought it was to good to be true.

Thx & Rgds
Growlos

 

[The Brown Growler]

I tried running the query for the 180 day sales with the criteria for Invoice Date set to:

InvoiceDate > [T_Dates].[SalesDate] -180

and also another option that I thought may work which was:


InvoiceDate Between [T_Dates].[SalesDate] -180 and [T_Dates].[SalesDate]

On both occasions the query failed to complete after several hours working on the data set of 674 dates. It may be the size of the records and the groupings that it just cannot cope with. For each of the 674 dates the calculations are working on a data set of nearly 1 million records. When it is all run in one query it just cannot process the data, perhaps if I left it overnight....?

Running the append query manually via the 3 sub queries with a single fixed date provided by the form text box takes 7 seconds to complete.

I think that in consideration of the fact that I have 674 dates to run it for then the Do Loop option is going to be my best choice. If anyone can please assist with my request in the original post in this topic with some VBA code then I would be most grateful.


Thx & Rgds
Growlos

 

[Galaxiom]

Glad you worked out I had left out half the criteria. I realised after I went to bed last night. You are probably right about the size of the query.

However you don't need to use your query through a form text box as you can read the values directly from a recordset. I have to go out for a few hours now.

Meanwhile read up on OpenRecordset and MoveNext to see how to work through a table. Your loop will be done using:

While Not EOF ' (EOF means End of File)
{Do stuff}
Loop

 

[The Brown Growler]

Galaxiom,

Thanks for your confirmation about the missing part of the criteria. When I tried the "Between" option as the criteria for [InvoiceDate] on the 30 day data set the results were in line with what I would expect.

If the form text box is not required to provide the date to the queries in the Do Loop all the better. I will have a read up on the topics you suggested. My lack of knowledge of VBA is where I get stuck, ie, writing the code etc.

Out of interest, in my queries using the form I used the referential for the invoice date as:

[Forms]![F_Date]![Text0]

How would this be addressed when the form was no longer in the process. Any help with the VBA most appreciated.


Thx & Rgds
Growlos

 

[The Brown Growler]

I have tried to write the beginning of the code but am getting in a bit of a tangle. Below is what i have cobbled together and it compiles OK but does nothing yet as I have not got the "engine" installed inside the Do Loop.

I set the below code up in a module named Loop_Sales_Status

Option Compare Database
Private Sub Loop_Sales_Status()
Dim db As Database
Set db = CurrentDb
Dim T_Dates As DAO.Recordset
    
    Set T_Dates = db.OpenRecordset("T_Dates", dbOpenDynaset)
    
    Do While Not T_Dates.EOF
    
        ' Now I am stuck re code to activate my append query which is a join of the 3 subqueries which use the date
        
        T_Dates.MoveNext
        
    Loop
    
End Sub

I can understand the logical sequence of what I am trying to achieve, however, putting it together is a bit of a struggle.

For Info:
The table containing the dates is named [T_Dates], the actual field containing the dates in this table is called [SalesDate].

The append query is named [Q_Append_SS] and the 3 sub queries which are joined in the append query are [Q_180], [Q_30] and [Q_10].

It is the 3 sub queries that use the dates from [T_Dates].[SalesDate] as a criteria for the [InvoiceDate] field.


Thx & Rgds
Growlos

 

[Galaxiom]

Now I am stuck re code to activate my append query which is a join of the 3 subqueries

The simple way is to paste your query SQL into a DoCmd.RunSql statement.
DoCmd.RunSQL "INSERT INTO blah blah"

Refer to the current record in the recordset as recordsetname.fieldname

Anything that does not work directly inside the SQL string can be included like this:
DoCmd.RunSQL "INSERT INTO blah " & variablename & "etc ... etc "

In a very similar style is the CurrentDb.Execute Method which in many way it is better suited to use in VBA. The difference is discussed here:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.gettingstarted&tid=b07f9a88-9f2e-4e07-80c1-57d7ae74eadc&cat=&lang=&cr=&sloc=&p=1