Row data referring to other row data? Please help.

[anasttin]

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.







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

[CJ_London]

you need a subquery

SELECT partnumber, pricechangedate, newprice-nz((SELECT TOP 1 newprice FROM myTable T WHERE partnumber=myTable.partnumber AND pricechangedate<myTable.pricechangedate ORDER BY pricechangedate DESC),0) as pricechange
FROM myTable

You should not store this value because it could change if you change the data and that will give you severe headache to resolve

[CJ_London]

Have been advised this is crossposted here

https://www.access-programmers.co.uk...=1#post1535387

OP advised

[anasttin]

Thanks for the assistance everyone. Ajax's code worked perfectly for my needs. Apologies for the cross-post as I wasn't aware it was an issue.

[CJ_London]

Recommend you advise the other forum and post the solution for their benefit

Also, a final suggestion, make sure your partnumber and pricechangedate fields are indexed otherwise performance will suffer with larger datasets

[anasttin]

Thanks again Ajax. How do you index a field in MS Access?

Recommend you advise the other forum and post the solution for their benefit

Also, a final suggestion, make sure your partnumber and pricechangedate fields are indexed otherwise performance will suffer with larger datasets

[CJ_London]

in table design view - select the field you want to index then then look at the properties at the bottom - choose either indexed (no duplicates) for primary keys and other fields where duplicates are not allowed and indexed (duplicates OK) for foreign keys and fields where duplicates are allowed.

You also have an indexes option on the ribbon. Use this to create multi field indexes and to fine tune existing indexes if necessary (primarily the ignore nulls option, used where a field contains lots of nulls but still needs to be indexed)

Any field which is regularly used in criteria and/or sorting should be indexed - see this link for more information https://www.access-programmers.co.uk...d.php?t=291268

[anasttin]

Hi all,

I was able to test and verify the subquery code that AJAX was kind enough to write for me (shown above) back when I posted this, but now that I'm adjusting the code to my real-life application, I'm getting a "syntax error" message.

My adjusted code is below... can anyone assist with troubleshooting it?

Much appreciate your help in advance.

-------------------------------------------------------------------------------------

SELECT [Full_Key], [_Effective_Date], [Price_in_US_Currency]-nz((SELECT TOP 1 [Price_in_US_Currency] FROM 1_T160_Adjusted_CATTLE_Data T WHERE [Full_Key]]=[1_T160_Adjusted_CATTLE_Data].[Full_Key] AND [_Effective_Date]<[1_T160_Adjusted_CATTLE_Data].[_Effective_Date] ORDER BY [_Effective_Date] DESC),0) as US_Delta_Price
FROM 1_T160_Adjusted_CATTLE_Data

[June7]

There is an extra ] after Full_Key in the WHERE clause. Need AS for the T alias and prefix T alias in front of the T fields.

SELECT [Full_Key], [_Effective_Date], [Price_in_US_Currency]-Nz((SELECT TOP 1 [Price_in_US_Currency] FROM 1_T160_Adjusted_CATTLE_Data AS T WHERE T.[Full_Key]=[1_T160_Adjusted_CATTLE_Data].[Full_Key] AND T.[_Effective_Date]<[1_T160_Adjusted_CATTLE_Data].[_Effective_Date] ORDER BY T.[_Effective_Date] DESC),0) AS US_Delta_Price
FROM 1_T160_Adjusted_CATTLE_Data;

Why a leading _ in [_Effective_Date]?

[anasttin]

Thanks a lot June7.

I'm now getting the error message "at most one record can be returned by this query" and I'm not sure why. Any ideas?

(The leading underscore in the "Effective Date" field is just how the data appears when imported. Looks ugly, I know.)

Thanks again for the help.

[June7]

Could there be multiple records with same date for a Full_Key? Review https://stackoverflow.com/questions/...39004#44939004

[CJ_London]

I agree with June

I think your original error was

... WHERE [Full_Key]]=[1_T160_Adjusted....

and to fix the current one

you need to change

....ORDER BY [_Effective_Date] DESC....

to

....ORDER BY [_Effective_Date] DESC, ID....

Where ID is the primary key (or at least a key unique within any fullkey/effectivedate grouping) to your 1_T160_Adjusted_CATTLE_Data table

[anasttin]

Hi June7 and Ajax,

I guess it's possible that there's records with the same date for Full key... I'll have to check at work on Monday.

I'll also try your suggested fix Ajax - (I think I know what you mean by "a key unique within any fullkey/effectivedate grouping" but will let you know if I'm just deluded).

Have a great weekend.

[CJ_London]

I guess it's possible that there's records with the same date for Full key

in which case you have another potential problem - if those records (same date, same fullkey) have different priceinuscurrency values - which do you want, the highest, the lowest, one in the middle somewhere, an average?

[anasttin]

I see the problem Ajax and, come to think of it, it would be very unlikely that I would have the same full key and date in the data. In fact, it almost certainly wont.

If it does by some chance, I'll have to resolve the problem with the base data.

Appreciate your help.