Query Multiples within a Time Frame

[jrub]

Hi All,


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


First post and Im really hoping that someone can help me out.

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.

If Study Period is defined as current year+study period = 2012+30=2042

Multiple expenditures take place until YearReplace+(X)LifeExpectancy>=2042

Where X is simply an increasing value, 1,2,3,4, and beyond.....

Can this be done in the expression builder???? or another way to have it as part of the query?

 

[Isskint]

welcome jrub

Query1 which I would like to add a record set called Expenditure Years
I take it Query 1 is based on another table? Is there a relationship between that table and Expenditure Years?

Could you post table structure or a sample of the DB?

 

[umchemist]

Hi

Below is a sample of the DB.

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

For example, if the item 1 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 4 - Item 2 -.....
and so forth....

 

[jrub]

Anyone out there whom may lend a very much appreciated helping hand?

 

[Isskint]

I can look later, i only have A2003 at work - cheap skates

 

[jrub]

Isskint,

Thank you very much! I sincerely appreciate it.

J

 

[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

 

[Isskint]

I may be missing a trick here, but i think the only option is to make a table via VBA/SQL.

• You would need to iterate through each record deciding
  • Does the Origin Year fall within your criteria (Start date and end Date)
    • Add this record to the table
    • Set a variable = to Origin + LifeExpectancy+OTLEA
    • Loop through adding the record to table
    • variable + LifeExpectancy
    • Check variable < End Date
    • Loop
• Loop

Have a look at the attached. It uses the data from your table. When you open the DB you will be presented with form asking for time period analyse. Once done it open the COPY table to show you which records comply along with each year they comply. The Copy table has 2 extra fields:
An entry ID
CYE = Year that row relates to

Now i have included all the fields in your table but this Copy table could be reduced to just the ReserveItemID and evrything else can be Dlookup()ed.

Hopefully it will give you some ideas.

 

[jrub]

Isskint,

Generating a table seems to most appropriate approach, I agree after more reading online.

Yes, the logic makes perfect sense and is what Im after.

One small change (if possible) would be that the origin year doesn't need to be within the start and end date, just the expenditures generated by that item within the start and end dates needs to be generated.

For example, using the data above, if an Item was built in 1997 (origin) and had a life expectancy of 4 and an one time adjustment of 2 (and the study period started in 2000) - we would get an expenditures needed in 2003, 2007, 2011, and additional iterations of +4, etc. until the end of the study period.


Thank you so much. I didn't bring my computer home tonight, though I plan to check it first thing tomorrow morning. Thank you so much for your help!!!

 

[jrub]

Finally got around to working on this, works great! Thank you so much for your help!

You made my weekend.

 

[jrub]

Isskint,

Can you help me out a tiny bit more if possible?

The first critical step in your method is determining if the expenditure occurs within the start and end dates- is it possible to slightly modify this?

I would like calculate all possible expenditures given the data set until the end date?

Results would still follow the one time adjustment logic and incremental values, BUT would produce all possible expenditure years until the end year date.

I've been reviewing your code, and sadly, am simply not good enough to sufficiently modify it to make it work.

Can you or anyone else help me out a bit?

 

[Isskint]

Use the Dmax() function to return the furthest date in your table. Replace the Me.tbToYear with DMax("[OriginalYearBuilt] + [LifeExpectancy]", "[ReserveItems]")

 

[jrub]

I made the replacement in the If statement as below:

While Not (rs.EOF) 'whilst not End Of File
        If (rs!OriginalYearBuilt >= Me.tbFromYear) And (rs!OriginalYearBuilt < DMax("[OriginalYearBuilt] + [LifeExpectancy]", "[ReserveItems]")) Then ' OYB starts during the specified period for Analysis
           'set variable to check running year
            intYearProgress = rs!OriginalYearBuilt
            While intYearProgress < Me.tbToYear

But I end of getting a compile error "wend without while" here:

 rs.MoveNext
        intProgBarAt = intProgBarAt + 1
        Me.lblProgIn.Width = ((intProgBarTot / 100) * intProgBarAt) * 9
        Me.Repaint
    Wend
End If

Did I do something wrong?

 

[Isskint]

That would be because you only replaced the first instance of Me.tbToYear.

Declare an extra Integer variable at the start of the procedure eg intMaxYear. Set intMaxYear = DMax("[OriginalYearBuilt] + [LifeExpectancy]", "[ReserveItems]") and use intMaxYear in place of Me.tbToYear

 

[jrub]

Thanks so much for your help!

I replaced all instances with intMaxYear, though when I try a start year of 2012 and an end year of 2042, I get zero entries.

If I comment out the following:

 If (rs!OriginalYearBuilt >= Me.tbFromYear) And (rs!OriginalYearBuilt < intMaxYear) Then

along with the corresponding End If

I get a list of possible expenditures. This is sweetness beyond satisfaction.

I can now generate a report that filters by year,

Thanks so much!