How to get query results that depend on the same query on anotherrecordset

[pinkfloydfan]

Hi there

I am very new to using access and in writing my first database I have
now come up against a problem that I can't seem to find a solution for
by googling and am hoping that someone here can point me in the right
direction.

I am using Access 2007 to build an inventory database. The core data
table includes the following fields:

ID
Asset Value
Depreciation
Old ID

In this table, Asset Value will be either a number (if it is the first
time we see the asset) or blank if we are looking at a child record.

I would like to run a query that calculates the Asset Value for all
the records at the end of the period along the lines of the following:

i) If IsNumber(Asset Value) then EndOfPeriod AssetValue = AssetValue -
Depreciation
ii) Else, EndOfPeriod AssetValue = EndofPeriod AssetValue (where ID =
OldID) - Depreciation.

The result should show a table of EndofPeriod AssetValues that are
getting smaller and smaller. For example, if there are 3 records only
in the table (parent, child and grandchild), the Asset Value for the
parent is 100 and there is Depreciation of 20 at each record, then the
EndofPeriod AssetValue at each stage would report 80, 60 & 40.

Is there a way to do this please?

Many Thanks
Lloyd

 

[Daryl S]

PinkFloydFan -

Oracle and SQL Server back-ends have great tools for making these
'unlimited' recursions work, but Access doesn't. You would be better off
re-designing your table. I would recommend the following, with the primary
key being the combination of ID and EndOfPeriod. You will no longer need
the Old ID, as you would use the same ID for the same asset throughout. Then
you can write a very simple query ordered by ID and EndOfPeriod to see what
you want. The EndOfPeriod would be a date field (e.g. end of month, end of
week, etc. - whatever period you want to use).

AssetID
EndOfPeriod
Asset Value
Depreciation

Normally in this case you would also have an Asset table where the primary
key is the AssetID, and it would contain things like the description of the
asset and the original asset value, date of aquisition, etc.

 

[pinkfloydfan]

Daryl

Thanks for your reply. I have created in my Assets table two new
fields: AssetGroupID and AssetGroupOrderID which if I run a query on
means I can extract all asset information and have it correctly
ordered in the output: the AssetGroupID is the same for each asset
while the AssetGroupOrderID sets the order for the assets in time
order. Together they describe a single recordset in the Assets table.

However, I am still unsure from your message how to use this to
implement the calculations I am trying to output.

Can you offer any further pointers please?

Many Thanks

Lloyd

 

[Daryl S]

PinkFloydFan -

If you can show us a sample of your data that would help, but I will assume
you now can show records that look something like this in your query:

AssetGroupID AssetOrderID AssetValue Depreciation
Asset1 1 500.00 20.00
Asset1 2 35.00
Asset1 3 20.00
Asset2 1 300.00 15.00
Asset2 2 12.00

If this is the case (Asset Value is only on the first record, but all other
records have depreciation), then you can get the current asset values in a
new query, based on your existing query. This will be a SELECT query with
totals included. Add the AssetGroupID field (Group By), and then create a
new field like this:

CurrentAssetValue: =nz([AssetValue],0) - nz([Depreciaion],0)

and set the totals row to SUM.

If your data looks different from this, show us some data, and we can help
more.

 

[pinkfloydfan]

Daryl

Thanks, I do understand this! However, perhaps I simplified my real
needs too much in order to provide an easily understood problem?

The calculations I need to run require me to know the value of the
asset at the end of each stage in its life separately: in the example
above, the real "Depreciation" value is the result of a Sum of other
related records and the AssetValue at the end of each period is also
affected by interest rates for that period.

So, the calculation for the end of period Asset Value =

(Starting Asset Value - Depreciation) / (1+interest rate)

(This is still a very simplified version of my requirements but it
does express all that I need).

Thus, just adding the total depreciation and taking that away from the
initial Asset Value gives the wrong answer.

If the only way to do this is through VBA, I'd be happy to write a
function to do that if you can direct me to the functions I will need
to use to access recordset data as I only have experience with Excel
VBA.

Thanks again,

Lloyd

 

[Daryl S]

PinkFloydFan -

The answer depends on how much information is stored for each period record,
and what can change. For example, can the interest rate change? Is it
dependent on time, or is there one interest rate per asset? Do you only
store the asset value once (at the beginning of its service life), and hence
we must calculate it for each stage?

If you have your database set up like a transaction system that stores all
the information needed for each period, then you should be able to get the
results with just queries.

If the data is such that you will need to calculate the ending asset value
in one period before being able to calculate the ending asset value of the
next period (because the ending value in one period is the beginning value
for the next period), then I would use VBA code for this. I like to use
temporary tables to hold this information as it is easy to test and use for
reporting, etc. My plan would be to write a query that pulls together all
the data for each period plus the beginning asset value, and then loop
through the periods. Each record in the temp table would hold the AssetID,
PeriodBeginDate, PeriodEndDate, BeginningAsset Value, PeriodDepreciation,
PeriodInterestRate, and EndingAssetValue. (If your calculation is more
complex, add whatever fields you need to verify your answer).

When this is done, run your queries, etc. using this data. Since it is in a
table, you can join this to other tables to get asset description, etc.
When you are done with this data, you can truncate or delete the temp table.

I have included the period begin and end dates to allow proper interest rate
calculations. You need to know if you compound interest or not, and will
need to figure the number of days to apply interest. I hope you don't need
to use 360-day calendars, but if so, let me know and I can post code for that
calculation. If your periods are regular (e.g. always on the quarter), then
things can be a little easier, but if you are talking asset stages, it may
not be.

As the problem gets more complex, so does the solution...

 

[pinkfloydfan]

Daryl

Thanks for your reply.

As you correctly point out, I have to calculate the end asset value
for one period before being able to calculate the end asset value for
another period because the end asset asset for one period is the start
asset value for another. So, I accept from your help that I will have
to write some VBA code to do this.

Can you point me to the VBA functions I will need to be able to
extract the recordset information before running the calculation?
And, once the temporary table is produced from the query, how do I
loop through all the recordsets in that query to produce this
calculation please?

Thanks a lot,

Lloyd

 

[Daryl S]

PinkFloydFan -

I pulled this from some older code I have (though this is in production and
working). I don't think you mentioned what version of Access you were on,
but this should work in all versions.

Start with this code below. You should put this in a module, and can then
call it from a button on a form or a macro. You will have to create the
temp table first. The temp table should include the asset id, the period,
the starting asset value, depreciation, interest, and ending asset value (and
any other items needed for the calcs).

Then create two queries. The first one is a delete query that removes all
records from the temp asset value table. The second one will populate that
table with one record for each asset / period. This query should put the
records in order, and will have the beginning asset value on the first record
of each asset, and sum or calculate the depreciation and interest for each
asset / period.

These query names can go in the code below. Also change the TempAssetTable
name below to whatever you used. The code below shows you how to open this
temp table and loop through the records, retaining the ending asset value
from one record and using it as the starting asset value for the next record,
unles the asset id changes.

Finally - work with this a bit, and if you get stuck, post your code in a
new thread with any new questions.

Public Sub CalcAssetValues()
'This procedure will calculate the ending asset values by period

Dim stDocName As String 'used for queries - great for debugging to
know which query errored out
Dim rsAssets As DAO.Recordset 'Temp Asset table
Dim response As Integer
Dim PriorAssetID as Integer 'whatever your asset id field type is.
Dim PriorEndingAssetValue as Double

On Error GoTo Err_CalcAssetValues


'First, run a delete query to remove all records from the temp table
' (create your own queries, and replace the names below)
stDocName = "TruncateTempTable"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Second, run a query to populate the temp table with all needed fields
stDocName = "AppendAssetDateToTempTable"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'Third, run code to calculate asset values

Dim dbs As Database
Set dbs = CurrentDb

'First, open temp table
Set rsAssets = dbs.OpenRecordset("TempAssetTable", dbOpenTable)

If rsAssets.RecordCount = 0 Then
response = MsgBox("No Records Found to Calc.", vbOKOnly, "No Assets
loaded in temp table.")
GoTo NoAssetData
End If
' set dummy value for first asset id - something the asset id will not be
PriorAssetID = 0
PriorEndingAssetValue = 0

' loop through temp asset table, calculating ending asset value for each
record.
While Not rsAssets.EOF 'stops when we get to the end of file
OutData.Edit 'lets you edit the temp table record
If rsAssets![AssetID] <> PriorAssetID Then
PriorAssetID = rsAssets![AssetID]
Else
rsAssets![BeginningAssetValue] = PriorEndingAssetValue
End If
rsAssets![EndingAssetValue] = Nz(rsAssets![BeginningAssetValue], 0) -
Nz(rsAssets![Depreciation], 0) + Nz(rsAssets![Interest], 0)
rsAssets.Update 'updates the record with the values just assigned
rsAssets.MoveNext 'moves to the next record in the table

Wend 'Until end of rsAssets file


NoAssetData:
rsAssets.Close 'Closes temp table


Exit_CalcAssetValues:
Set rsAssets = Nothing 'releases memory for object
Set dbs = Nothing
Exit Sub


Err_CalcAssetValues:
MsgBox Err.Description
Resume Exit_CalcAssetValues
End Sub