Calculating from different records

[Tskutnik]

I need to calculate the difference in a stock price from one (user defined) day to another. Being new to access my issue is how to do this given that the records with the beginning and end dates are different and the data is not in the same row. e.g.
Key # Stock Date Price
1 IBM 1/2/15 95
2 IBM 1/3/15 100
3 IBM 1/4/15 101
4 IBM 1/5/15 102

If the user selects the run dates from 1/3/15 to 1/4/15 the math for a price change would be 101/100. How can I do this calc without the data being in the same row? I can do a union query and create the data all on one row but I'd think there is a more efficient way to do this through a formula in a module.
Thanks

 

[jdraw]

see the datediff() function

Your question suggests you have a spreadsheet background. Database is quite different.
You should do some googling to find out more.

 

[CJ_London]

there are a number of ways, which is the right one depends on the data.

Your example dataset has consecutive days - is this always the case or can there be gaps? (e.g. perhaps weekends or public holidays where there is no trading so no new price or simply the data has not been captured)

 

[Trevor G]

I would consider using some vba to send to Excel and do the calculation there then import the results in the background, access doesn't like looking at one row compared to another when checking for results and criteria. Like an IF statement if the record below states this then do this if the record above states something else do this.

 

[CJ_London]

access doesn't like looking at one row compared to another when checking for results and criteria

Sorry, don't agree - it just needs a value to determine the before or after - in this case the date.

The reason for needing to know whether there are missing dates is because there is a simpler method if there aren't any missing dates

 

[jdraw]

I don't think the OP has provided info to make a comment about Excel. He/she has posted in an Access forum, so by default we're talking database/Access. Now we need more detail
about the specific issue the OP is trying to solve.