Calculate between recordsets on Form

[dwatt]

On form I have 2 recordsets with fieldnames A,B,C

I want to calculate a new value for A in recordset 2 using B and C in recordset 1:

An = (Bn-1 * Cn-1)/10

B....C.....A
10...2....0
15...25..2

'A' will have an initial value of 'o' and B,C are precalculated values.

How can I go about calculating between recordsets to achieve this in Access?

Thank you for any help

 

[dcx693]

On form I have 2 recordsets with fieldnames A,B,C

Do you mean you have 2 different "fields", or 2 different "columns"? Meaning that you have a table feeding your form that has data in 2 separate columns and you'd like to calculate a value from them?

 

[dwatt]

Apologies

I mean on form I have 2 rows with fieldnames A,B,C

 

[dcx693]

OK, I think I understand what you want to do. You didn't label your rows n=0, n=1 so I didn't get it at first.

These types of calculations are best done in a query. That query can then go on to feed data into your form.

Spreadsheets like Excel are best used for these types of calculations, but you can do them in Access with queries. Are you sure you'd rather not use Excel? If not, I can help you with doing it in Access.

 

[dwatt]

I would rather do in Access if possible as this is the final piece in fulfilling my database requirements....I think.... so help here would be gratefully appreciated.

Thanks

 

[dcx693]

How many values of N do you want to calculate the result for?

 

[dwatt]

Could be 100's could be 1000's....I would like to keep that open.

Cheers

 

[dcx693]

Silly me. It's best to calculate these values using a recursive function. Doesn't matter if it's Excel or Access. It just boils down to writing a custom function. I'm working on it now.

EDIT: Nope, I'm going to go back on what I said. It's not at all a recursive function unless An depends on An-1 and it doesn't look like it does. Nor does it look like Bn or Cn depend on Bn-1 or Cn-1. Am I missing something?

 

[dwatt]

Fields B and C are numbers which are automatically filled in.
Field A in the current record (say n=1) is dependant on the previous record (n = 0) for fields B and C and so on…

(B and C in the previous record are multiplied together and 10% of that value is placed in the next record for A which is used for updating D)

n….B…..C…..A…D
0….10…2…..0…12
1….15…6…..2…23
2….6…..10…9…25
3….20…4…..6…30

D = B+C+A

If there is no value in record for A (when data entered for the first time) then A is assigned the value 0:

A0 = 0

Then

A1 = (B0+C0)/10
A2 = (B1+C1)/10
A3 = (B2+C2)/10

This looks so easy but sadly it has beaten me…I am desperate to resolve….suggestions gratefully appreciated

Cheers

 

[dcx693]

OK, so calculating both D and A, for any value n, depends on having the values of B and C for n-1. That much I got.

However, it only depends on having the values for n-1. It doesn't depend on n-2, n-3, etc...

For example, if n=2, calculating A=9 and D=25 depends only on having the B and C values for n=2 and the values of B and C for n=1. It (apparently) is irrelevant that for n=0, B=10, C=2, A=0 and D=12.

What I'm saying is that based on what you've written, calculating A and D for n=1000 only depends on having B and C for n=1000 and having B and C for n=999. IOW, if you give me the values for n=0 and n=1, I can't use those to calculate n=1000 because (based on what you've shown):

Fields B and C are numbers which are automatically filled in.

.

Again I ask, am I missing something? Is that all you want? A formula to calculate n from n-1?

 

[Pat Hartman]

In a relational table, records do not have any particular order. I know this is a hard concept to grasp because obviously the records aren't floating in space, they really do exist in some physical order on the disk drive. But only the database engine reads the physical table. Application programs read recordsets created by SQL. The records are retrieved from the table in what ever order is convenient for the database engine and a particular order is imposed only if the SQL used to create a recordset includes an order by clause. This is very different conceptually from the flat file model of the spreadsheet where each row follows another and that order can be predicted without sorting. Also in a spreadsheet the intersection of a row and column which is called a cell can be addressed uniquely by specifying its row and column designations. This is not the case with a recordset. Columns are addressed by their names but rows can only be specifically retrieved if you know their primary key value.

It is certainly possible and actually quite easy to perform the requested calculation in a report or in a VBA code loop. That is because both processes read one record at a time from the beginning to the end of the recordset and you can include code that saves the value from one record for use in calculating a field for another record.

It is quite difficult to create SQL to perform the calculation in a query. To do this calculation in a query, requires that each record have a sequentially assigned unique identifier with no gaps so that the primary key of the current record minus 1 will equal the primary key of the record whose values you need for your calculation. If and only if this is your situation, you can create a query that joins the table to itself. Start by creating the query with the QBE grid. I do this to save typing. Add the table to the grid twice. Draw the join line from instance 1 to instance 2. Select the columns necessary to do the calculation. Switch to SQL view. You will need to change the join to:

From YourTable Inner Join YourTable_1 On YourTable.YourKey = YourTable_1.YourKey -1

Once you do this, you will never be able to go back to QBE view. Modify the rest of the query to perform the calculation
.

 

[dwatt]

In answer to dcx693
Yes, I suppose it is as simple as that , a formula to calculate n from n-1. The problem has been shifting that value into the next field next record…so I guess the question is “am I missing something” to which you may throw back (and hopefully) “you sure are….”
I thank you for any help you may be able to offer.


In answer to Pat Hartman
I am going to have to read up on this as you maybe way over my head...I've never dealt with 'instances' before and have very little knowledge in VB code to create that loop which you say would easily perform the requested calculation.


Regards to you both

 

[Pat Hartman]

Let's start with some direct questions.
1. Does your table contain a unique identifier that starts from 1 and increments by 1 without interuption for the entire recordset?
2. Are you intending to store the calculated value?
3. Do you have a set of data that needs to have the values calculated or are you starting from an empty table and so only have to do this for each new record as it is added by the form?

 

[dwatt]

1. No. ‘B’ is a unique identifier for the record for that year ‘A’, and this is where the current and previous records come in. (see table below)
2. ‘E’ is a calculated field that multiplies ‘C’ and ‘D’
3. ‘F’ is determined from autolookup tables
4. ‘G’ is a calculated field that takes 10% of ‘E’ from previous record and subtracts from current record in ‘F’



A……....B…… C……D…….E…………F…………...........G
2001…1…….2……40……80…………………………
2002…1…….3……30……90……..110……(110-8)=102
2003…1…….2……60……120…..115……(115-9)=106
2001…2…….5……40……200…………………………
2002…2…….3……50……150…..110……(110-20)=90
2003…2…….4……60……240…..115……(115-15)=100

Am I looking for something along the lines

G = (Select E from FormName Where A = year - 1) - (Select F from FormName Where A = year)


I hope I am making myself clear

Cheers

 

[Pat Hartman]

You can use DLookup() to obtain a single column from a row that you can uniquely identify.

PrevE = IsNull(DLookup("E", "YourTable", "B = " Me.B & " AND A = " Me.A - 1), "")

 

[dwatt]

Created a new field expression PrevE as below and came up with an invalid syntax error.
What does the Me keyword signify
Have I included all operators....

PrevE = IsNull(DLookup("E", "Table1", "B = " Me.B & " AND A = " Me.A - 1), "")

Thank you for your patience..
Regards

 

[Pat Hartman]

You have never placed your request in context. I don't know when or where you need to do this. The Dlookup() solution was intended to be placed within a form event. If you want to place it somewhere else, the syntax will be different.

As the controlsource of some control:

=IsNull(DLookup("E", "Table1", "B = " & " AND A = " [A] - 1), "")

 

[dwatt]

I would like it to be a calculated field expression in Field G of the query design view window.

G: (DLookup("E", "Table1", "B = " & " AND A = " [A] - 1), "")

Embarressed...

 

[dwatt]

sorry

G: IsNull(DLookup("E", "Table1", "B = " & " AND A = " [A] - 1), "")

 

[Pat Hartman]

Sorry, I left out some ampersands:

G: IsNull(DLookup("E", "Table1", "B = " & YourTableName. & " AND A = " & YourTableName.[A] - 1), "")

BTW, using DLookup()'s in queries is inefficiency in the extreme. I hope your recordset is small or the query will take forever to run.