Prior records' values in a query calculation

[mwething]

Hello! I am new to the forum and to access. I am converting an Excel file to Access. I have a query that averages two values from a data table and returns that value in query field #7. In the query field #8, I want to perform the following calculation: (prior record's average-two prior record's field #5 value) + (prior record's average).




In Excel the formula is =(AVERAGE(B4:C4)-C3)+AVERAGE(B4:C4) where AVERAGE(B4:C4) is the prior (day's) record's average, B4=High of the prior day, C4=Low of the prior day, and C3 =Low of two (day's) record's prior.


My query field names are: Id, MktDate, Open, High, Low, Close, Average[high]+[low]/2), and Projection.

I do not know SQL.

Thanks for any help and/or suggestions.

[Micron]

EIDT - I removed the stuff re the calculation as I obviously goofed it.

To get a value from a prior record you need a subquery if doing this via sql (queries).
Check for more info: http://allenbrowne.com/subquery-01.html

or it can be done in code.

More often than not, those who convert Excel data to Access tables invariably do it all wrong. If you have not studied and learned db normalization I'd strongly suggest you stop and do so.

[mwething]

EIDT - I removed the stuff re the calculation as I obviously goofed it.

To get a value from a prior record you need a subquery if doing this via sql (queries).
Check for more info: http://allenbrowne.com/subquery-01.html

or it can be done in code.

More often than not, those who convert Excel data to Access tables invariably do it all wrong. If you have not studied and learned db normalization I'd strongly suggest you stop and do so.

Thanks Micron. I will study the material at the link you provided and see if I can figure it out. I think I am looking for a formula/equation/expression as used in Excel but such might not exist in Access.

[Micron]

A lot of Excel functions are not in Access as they're more suited to spreadsheets.
For a list I often use
https://www.techonthenet.com/access/functions/index.php

For a complete list (by alpha and type - see math and aggregate for your task I think)
https://support.microsoft.com/en-us/...2-658ce330ed83

Don't overlook what I said about db normalization when coming from Excel.

[NoellaG]

Hi, when working in excel, all values have a specific place and order, so you know what the previous record is. In a database all records have no order at all, until you specify an 'order by' clause in your SQL. So what is the previous record will always depend on the order by criteria in your query.
So when you order by field1, the previous record is the record where field1 = max(field1) and where field1 < Current value of field1, assuming you don't have doubles in field 1.

[mwething]

Thanks Micron and NellaG. I think I have a lot of learning ahead! Perhaps I need to head to Fiverr. :-)

[ArviLaanemets]

Hi, when working in excel, all values have a specific place and order, so you know what the previous record is.

This is a way to think about your Excel data like a paper table (and it looks like many of Excel users do this)! User can always sort the Excel table by any field or combination of fields, and any results calculated by such formulas will have totally different results at once. So you can calculate values in such way only when the order of source data doesn't affect anything. Otherwise to avoid your design being sloppy you must register date/datetime for every record, or hard-enter some record number.