Do While Loop Macro for Query Design

[jrub]

Hi All,

Very new, but I thought I would post here as I imagine a solution would be a macro that one passes variables to...

Hi All,

I am a huge NOOB here, so please bare with me

I have Query1 which I would like to add a record set called Expenditure Years to.

The Expenditure Years are calculated based on multiples of the YearReplace record set, the sum of Year Replace and LifeExpectancy, until the end of the study period.

Basically, expenditures occur as iterative multiples until a criteria is reached.

For example, if the item was built in 2000, and has a life expectancy of 5 years, and has a life expectancy adjustment of 2 years (only is applied to first iteration), the multiples would list as below; until the base year+study period sum is reached.

Example: base year (1990) + study period years (30) = 2020
Expenditure 1 - Item 1 - 2007
Expenditure 2 - Item 1 - 2012
Expenditure 3 - Item 1 - 2017

it would stop here for Item 1 as 2017 + 5 >= 2020, BUT would continue to Item 2 and do it all over again.....

Expenditure 3 - Item 2 -.....
and so forth....


Can anyone come up with a VBA macro that accomplishes this? Any help is sincerely appreciated

Thank you

 

[jrub]

Anyone have an idea for this?

 

[mdlueck]

It sounds more like you are seeking to populate a table with projected calculations per year rather than actually query data which is already in a table, correct?

Or jump over to Excel and use VBA to create the calculated matrix in an empty range on a sheet.

Is this what you are seeking to do?

 

[jrub]

Some additional explanation.....

I'm trying to build a query which lists all expenditures associated with multiple items, within a time period. The time period is the defined by the start year (BaseYear) and the period of analysis (AnalysisPeriod) + BaseYear. Thus is the base year is 2000, and the analysis period is 30 years, I'm querying all expenditures which occur between 2000 and 2030.

Expenditures are defined from the following variables:

Item Description, Year Built, Life Expectancy, One Time Life Expectancy Adjustment

Expenditures occur at the end of the life expectancy to renew the item. The trick is that the life expectancy of an item is modified once by the "One Time Life Expectancy Adjustment" value, then afterwards the follows the "Life Expectancy" value. There are multiple expenditures per item based on the life expectancy of them item within the time period of analysis. I would like to build a query which develops a list of all expenditures.

For example, I have three items.

My original raw data is the following:

TITLES: Item Description, Year Built, Life Expectancy, One Time Life Expectancy Adjustment

Item 1, 2000, 5, 2
Item 2, 2001, 7, 1
Item 3, 2005,10,0

The query would produce the following data from the above information if the BaseYear is 2000 and the analysis period is 20 years (this produces the end year of 2020):

TITLES: ExpenditureID, Item Description, ExpenditureYear


Expenditure 1, Item 1, 2007
Expenditure 2, Item 1, 2012
Expenditure 3, Item 1, 2017
Expenditure 4, Item 2, 2009
Expenditure 5, Item 2, 2016
Expenditure 6, Item 3, 2015


Recap:
Item 1 was built in 2000 and would have it's first renewal in 2007, because the life expectancy + one time adjustment is 7 years.
After this, it would need to be renewed in 2012 because the life expectancy of a new item is 5 years and it was last renewed in 2007.
Item 1 is then again renewed in 5 years since the last renewal (2012) and need to be renewed against in 2017 and so forth until renewal >= the end year, in this case 2020.

This listing would occur for all Items.

I really don't even know what to call this......can a macro be written so that it could be implemented into a query??????

Thanks so much, and I am sorry for my crazy descriptions....I thank everyone who can contribute

 

[jrub]

I have a pile of varying data all the time that once all the expenditures are generated, make using access for reporting so much sweeter....that why im trying to use access

 

[mdlueck]

I really don't even know what to call this......can a macro be written so that it could be implemented into a query??????

Perhaps you "could" get all of that business logic articulated into SQL in general... something along the lines of a SQL Query defined on a SQL database server as a Stored Procedure! It sounds to me fairly significant to accomplish in pure SQL.

I think better off is to have a database table, some VBA code performing the calculations, creating records based on the calculations, and finally do a SELECT [cols...] ORDER BY [col1] type query to display the results in sorted format.

 

[jrub]

Makes sense, I am guessing an SQL query could also be a bit slow.

How does one even start with the vba macro to develop this ? Can you point me in the right direction?

Should I post a sample db?

 

[ajvol01]

Makes sense, I am guessing an SQL query could also be a bit slow.

How does one even start with the vba macro to develop this ? Can you point me in the right direction?

Should I post a sample db?

Take a look at the attached db and code and let me know if this works ok.

Function LoadExpenditures()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strTable1 As String
Dim strTable2 As String
Dim intExpNo As Integer
Dim strExpNo As String
Dim strItem As String
Dim lngExpYear, lngLifeExp, lngExpAdj, lngStudyPer, lngBaseYear, lngEndYear As Long
Dim strSQL As String
strTable1 = "tblLifeExpectancy"
strTable2 = "tblExpenditures"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(strTable1)
' Clear old expenditure data
strSQL = "DELETE FROM " & strTable2
db.Execute strSQL
With rs
    
    Do While Not .EOF
    ' set expenditure year = 1
        intExpNo = 1
        strItem = .Fields("ItemNo").Value
        strExpNo = "Expenditure  "
        lngLifeExp = .Fields("LifeExpectancy").Value
        lngExpAdj = .Fields("LifeExpectancyAdj").Value
        lngStudyPer = .Fields("StudyPeriod").Value
        lngBaseYear = .Fields("BaseYear").Value
        lngEndYear = lngStudyPer + lngBaseYear
        
        Do Until lngEndYear < lngExpYear + lngLifeExp
        
        strExpNo = Mid(strExpNo, 1, Len(strExpNo) - 1) & intExpNo
        
        lngExpYear = lngBaseYear + lngLifeExp + lngExpAdj + lngLifeExp * (intExpNo - 1)
            
        strSQL = "INSERT INTO tblExpenditures ( ExpenditureNo, ItemNo, ExpYear ) " _
            & "SELECT " & Chr(34) & strExpNo & Chr(34) & "," _
            & Chr(34) & strItem & Chr(34) & ", " _
            & lngExpYear _
            & " FROM " & strTable1 & ";"
            
            Debug.Print strSQL
            
        db.Execute strSQL
            
        ' increment Expenditure year by 1
            intExpNo = intExpNo + 1
        Loop
        .MoveNext
    Loop
    .Close
End With

Set rs = Nothing
Set db = Nothing
End Function