recursion in Access?

[DB88]

Hello,



I'm new to Access and need to implement a recursion calculation. Is that possible using relationships and/or query? I've been trying to figure out a method for over a day. Inventory in year i = new arrivals in year i + (inventory from year i-1)*(attrition rate).

I wrote a query that can calculate the inventory of a given year that I specify in a parameter. But I need the inventory for multiple years at once without having to manual fire the query 30 times.

I don't know visual basic and am trying to avoid the extra time needed to learn it. But i'm starting to think that may be the only way. Anyone have ideas or should I just give in and learn VB?

[ranman256]

You would probly get a memory error (or fill the DB limit) of queries running subQueries.
Recursion works in code, no need for it queries with recordsets.

[DB88]

I would really like to write a function in visual basic that performs the recursion I need. Then I could create an expression in a query using that function.
But how do I write a function that can get values from multiple records?

I want to create a query that finds the inventory for every year (about 30 years). I've written the inventory calculation above. The terminal statement is when year = 0 then Inventory = new arrivals in year 0. So, if query is trying to find inventory in year 10, the function would need to step through records associated with year 0 - 10. Is that possible?
Or should I just write a subroutine the loops through each year record in the table, and finds the associated inventory?

[ranman256]

I still dont see the need for the function...in a query the range would be:
select * from tbl where [DateEvent]
between [dateStart] and [dateEnd]

or
between date() and dateAdd("yyyy",-30,date())

[DB88]

Sorry, I don't think I understand. New to Access so havn't gotten the lingo down.

Did you mean create a query where you select * (instead of specific fields) and set the criteria to [yearfield] is between [startDate] and [endDate]?
if yes, that query would only give me the records I need to calculate inventory for one year. Plus I would have to either hard code the year I'm looking for or create a query parameter to specify the year. would then have to repeat that query 30 times, stepping through each year. That step through would need to happen in a VB function.

[ranman256]

If you want all dates then you wont need the WHERE clause about date range.
Just get all records for all customers.

But if you only want today thru 30 years, then... between date() and dateAdd("yyyy",-30,date())
gets everything between today minus 30 years. Today changes everyday.

I still may be missing your intentions.

[DB88]

I think there is a miscommunication on what I want as the result of the query. Let me explain

I have a table, Procurement, whose key is year (fieldname = YearKey). There are 30 records, one for each year between 2000 and 2030. The table also contains the field, Acquisition, which is the number of new products we will procure in a given year. What I need is the inventory for each year. So I would like the results of a query to be 30 records (one for each year 2000-2030), with the year and inventory. The formula for inventory is current year acquisition + (last year's inventory)*AttritionRate where AttritionRate is a constant.

For year 2000, Inventory is just Acquisition in year 2000 (base case or termination statement)
For year 2001, Inventory = Acquisition in year 2001 + (Inventory of 2000)*AttritionRate
For year 2002, Inventory = Acquisition in year 2002 + (Inventory of 2001)*AttritionRate
and so on

I have the table Procurement:
YearKey Acq
===== ==
2000 2
2001 3
2002 4
2003 5

Assume AttritionRate is 0.5, I want the result of my query to be:

YearKey Inventory
==== =====
2000 2 (base case)
2001 3+2(0.5)=4
2002 4+4*(0.5)=6
2003 5+6*(0.5)=8

[ItsMe]

A function that calculated attrition rate may be in order. Have not thought the whole thing through but could probably have your query call the function. I consider that a good approach when doing calcs like statistics and averages. Not that I am a statistics major or pro, I am not.

What would your formula for attrition rate look like?

[DB88]

I don't need a function for Attrition Rate. It will be a constant

[ranman256]

I see now....I dont think recursion queries would work because it would quickly eat all the memory.
BUT it could be done as step by step code (or queries) by writing to a 'results' table.

Ex:
Q1: post the 1st year values to the results table
Q2: take the next year + sum of the results table of the previous year...post to results
repeat until your range of 30 ends.

That would prob. be the best method.

[ItsMe]

All you have left as a variant is the previous year inventory. Some example code here, towards the bottom.
http://support.microsoft.com/kb/101081

[DB88]

"Ex:
Q1: post the 1st year values to the results table
Q2: take the next year + sum of the results table of the previous year...post to results
repeat until your range of 30 ends."

Do mean that I should write a code that does this? So, I have to learn Visual Basic in order to make calculate Inventory? (Bummer)

[ranman256]

I completed this with 4 queries to add a single year data..then you would run it 30 times.

A form with the starting year text box...txtYr
Q1 counts the inventory for year (shown in txtYr)
Q2 counts the inventory for prev. year in tResults (shown in txtYr-1)
Q3 summs the 2 previous queries together (UNION QUERY) so you get CurrYrAq + (PrevYrAq * .5)
Q4 posts results to tResults for 1 year (shown in textbox)

when Q4 finished , then txtYr increments by 1
run query4 again.

repeat until 30 years goes by.
NO CODE NEEDED.

[DB88]

http://support.microsoft.com/kb/101081 pointed me to Expr1: DLookUp("[Field1]","Table1","[ID]=" & [ID]-1) which gets me the previous record. That is a huge step forward. Thanks!

[ItsMe]

http://support.microsoft.com/kb/101081 pointed me to Expr1: DLookUp("[Field1]","Table1","[ID]=" & [ID]-1) which gets me the previous record. That is a huge step forward. Thanks!

The domain function may be suitable. If you have to use more than one domain function within a given query object, consider the user defined function that employs DAO as an alternative (further down the page in the link).