How to iterate a calculation across values within an Access table

[jviel33]

Hello everyone,

I have created a table in Microsoft Access 2013 that contains pricing information. The first column of the table contains an ID number populated by Access, the second column contains a short-text name "Node" and the third column contains a number "Price".

My ultimate goal is to iterate across all values within the second column "Node" of the table, holding one constant at a time to calculate the difference between two nodes and create a new table with these calculated values. I have experience writing VBAs in Excel, but I am having a difficult time finding a good source to get started. Any help would be much appreciated.

(Current Table)

ID Node PriceA
1 A 10
2 B 15
3 C 20


(Desired Macro Calculation)

Node Node Price
A A 0
A B -5
A C -10
B A 5
B B 0


... ... ...


Thank you in advanced,
Jeff

[June7]

This would require lots of VBA code manipulating recordsets within looping structure and writing records to table.

[orange]

I agree with June. In fact I think Excel is more suited to your requirement -especially since you have experience with Excel.

[rpeare]

I think this would be pretty easy you can try this and tell me if it is what you want or not:

Assume your dataset as you've explained it is called tblTest

Create this query

SELECT tblTest.node AS RowNode, tblTest.pricea AS RowPrice, tblTest_1.node AS ColNode, tblTest_1.pricea AS ColPrice
FROM tblTest, tblTest AS tblTest_1;

Call this query qryCartesian

then create this query:

TRANSFORM Sum([rowprice]-[colprice]) AS Expr1
SELECT qryCartesian.RowNode
FROM qryCartesian
GROUP BY qryCartesian.RowNode
PIVOT qryCartesian.ColNode;

this should give you a matrix showing you what you want (I assumed you wanted a matrix).

If you don't want the matrix you can do this with a single query:

SELECT tblTest.node AS RowNode, tblTest.pricea AS RowPrice, tblTest_1.node AS ColNode, tblTest_1.pricea AS ColPrice, [rowprice]-[colprice] AS Difference
FROM tblTest, tblTest AS tblTest_1;

[jviel33]

Thanks rpeare! This is exactly what I was looking for

[June7]

Rats! How embarrassing, I should have remembered Cartesian relation. Glad you have a working solution.

[rpeare]

it's only fair june, I don't know how many times you've caught stuff I've missed.

[orange]

I think you should get some sort of guessing prize for deciphering (correctly) what the OP was thinking.