Row data referring to other row data? Please help. (1 Viewer)

Hi everyone,

I have a table of data with 3 fields: "part_number", "date_of_price_change" and "new_price", but I need to convert the "new_price" field to show the "price_change", rather than the full "new_price" of each part on each date.

This will obviously involve some process that looks at each unique part number on each date and looks up the price of the record with the same part number with the next earliest date and deduct the prices to get the price change (see example below).

Problem is, I have no idea how to do this in Access and there are too many records for Excel. Can anyone assist with how to do this in Access? (Note that date changes can happen any time and are not periodic).

Many thanks in advance.

Ana


Example:
===============================================
Current Table Data:

part_number date_of_price_change new_price
ABC123 1/2/17 120.05
DEF321 6/5/17 198.30
ABC123 2/6/16 99.00
ABC123 1/1/15 80.00

===============================================
Desired Table Data:

part_number date_of_price_change price_change
ABC123 1/2/17 21.05
DEF321 6/5/17 198.30
ABC123 2/6/16 19.00
ABC123 1/1/15 80.00

This is usually done with a subquery. The Get the value in another record section of this web page should get you started.

for the benefit of responders - crossposted here

http://www.accessforums.net/showthread.php?t=66588

@Anasttin - I see you have crossposted all of your threads to date. You have not been advised of the expected etiquette for crossposting - please read this link http://www.excelguru.ca/content.php?184