Iteration in code rather than queries?

[torok]

I'm writing a predictive modeling program with Access 2k wherein the data for each year is dependent on the data calculated for the year before it. So right now I have one query for each year that I'm predicting information, and the criteria limits the input data to the year before it. (i.e. the 2006 calculation query grabs data only from Year 2005 in my INPUT table, has a couple of calculated fields, and an append query appends the 2006 calculated data to the INPUT table and then I run the 2007 query, and so on). Eventually I end up with one table containing every year's calculated information. Essentially, I perform an iterated loop.

Rather than writing one calculation query for each year, referencing data from the previous year's query results, I was thinking this might be easier if I could just get my recordset into memory and do all the calculations in a VBA loop.

Does that explanation make sense? Has anyone tried this before, and is that a good idea? I'm not too good with ADO recordsets yet, but I can learn.

 

[llkhoutx]

A series of queries would be faster than utilizing a recordset.

Use VBA querydefs to generate and run each query inside a loop, saving the data you want for use in the next query and/or its criteria.

 

[torok]

I'm intrigued by your suggestions, but I'm not 100% certain what you mean - Do you mean that I should write each query as SQL in VBA as opposed to creating the stored queries?

 

[Travis]

There is a way to get the benifits of a QueryDef without having to recreate it each time the code runs (preventing bloat).

First Create an Append Query

Example:

INSERT INTO tblProjection ( [Year], Field1, Field2, Field3 )
SELECT Last([Year]+1) AS NYear, Last([Field1]*1.1) AS NField1, Last([Field2]*1.5) AS NField2, Last([Field3]*2) AS NField3
FROM tblProjection;

Notice the use of the Aggragate function Last. This will produce the last Year Record, do calculations on it and then append it to the table. Each time you run this query it will get the Last year and calculate it and append a new record to the table. This being the case you can then run the following Code:

Function AddAdditionalYears(ByVal intYears) as Boolean
On Error Goto ErrorHandler
Dim db as Database
Dim iLoop as Integer

     Set db = CurrentDB

     For iLoop = 1 to intYears
          db.Execute "[i]qry's Name[/i]"
     Next iLoop
     AddAdditionalYears=True

Exit Function
ErrorHandler:
AddAdditionalYears=False
Err.Clear

End Function

Thus you gain the benifits of a compiled QueryDef and the flexability of code (You don't have to sit there and run the query 150 times yourself )

 

[torok]

That's a brilliant idea, thanks! Unfortunately I was not as clear as I apparently should have been when defining the problem, so I will ahve to find a way to modify this solution. I don't get one row for every year - I get several rows, each with its own information.

Currently my Select queries, that do the calculations, look like this:

Select DistinctRow [tblProjection].field1, [tblProjection].field2, 
(1.5*[tblOne].[A]+1.5*[tblTwo].[A] AS expr1,...  WHERE [tblProjection].Year = 2005

Then I have to do an append query. If I were to use and aggregate LAST query it would only give me the last row instead of the last few rows that I need (i.e. there is more than one primary key field in the table - the Year is just part of it).

For example, currently my select query (that performs calculations) for 2005 joins three tables (including tblProjection) and gives me data for 4 professions and 2 genders, resulting in 8 calculated rows (and about a dozen columns). The query for 2006 is exactly the same, except that as part of the criteria it restricts the incoming data on tblProjection to data corresponding to the year 2005. I have 20 Select queries and 20 Append queries at the moment (to project 20 years into the future), and they're all nearly identical.

This may not make any sense to anyone but me at this point

I'm sure your new method can be incorporated into this somehow, but it'll take a bit more thought on my part.

Thanks again though, that was a fantastic solution.

 

[Travis]

Then all you need to do is not use Last on the professions and genders, instead use Group By. This will give you the Last for each profession and gender.

 

[torok]

Doh! Of course, I should have thought of that

Many thanks! Brilliant solution, and it works wonderfully.