Comparing Row N with Row N+1 to Calculate a new Field (1 Viewer)

[caprice63]

Hoping for a little more help please.
I have a database which has many fields including:
Location, Date, Time, GenRate, SysData, etc.
There will be several records having the same Loc/Date/Time and different GenR/SysData etc. (sample data below).
I did some research and think I may need to use “Recordset” but I don’t really understand how to apply it.
My problem:
I need to first sort the data by Date, Time, Location, GenR (old to new, old to new, ascend, descend).
I then need to create another field, in the relevant Loc/Date/Time set, “Gen%” and populate it with a percentage which is calculated by taking the difference between Row1 GenRate and Row2 GenRate and then dividing by Row2 GenRate to show how much bigger Row 1 is over Row 2 as a percentage. Then do the same for Row 2/Row 3 etc for all the same Loc/Date/Time rows.
I have previously done this using an Excel table and the formula (on row 2 of my table) was:
=IFERROR(IF(AND(+[@Loc]=B3,[@Time]=C3,[@Date]=D3),([@GenR]-AR3)/AR3,0.01),0.01)
The IFERROR was needed to provide a nominal value for the last item in the relevant Loc/Date/Time set.
NEXT, I need to sort the data by Date, Time, Location, SysD (old to new, old to new, ascend, descend).
And then I need to repeat the steps above in a new field called “SysD%”

Thanks in advance for any suggestions.

SAMPLE DATA
Location Date Time GenRate SysData
London 01/10/2018 12:30 16 1
London 01/10/2018 12:30 11 13
London 01/10/2018 12:30 26 24
London 01/10/2018 12:30 58 62
London 01/10/2018 12:30 61 71
London 01/10/2018 12:30 65 100
Swansea 01/10/2018 13:50 37 1
Swansea 01/10/2018 13:50 64 16
Swansea 01/10/2018 13:50 47 30
Swansea 01/10/2018 13:50 83 99
Glasgow 01/10/2018 14:10 38 1
Glasgow 01/10/2018 14:10 69 7
Glasgow 01/10/2018 14:10 45 22
Swansea 01/10/2018 14:50 54 54
Swansea 01/10/2018 14:50 43 61
Swansea 01/10/2018 14:50 69 80
Swansea 01/10/2018 14:50 63 100
Swansea 01/10/2018 15:20 49 1
Swansea 01/10/2018 15:20 28 11
Swansea 01/10/2018 15:20 49 55
Glasgow 02/10/2018 14:10 58 41
Glasgow 02/10/2018 14:10 51 44
Glasgow 02/10/2018 14:10 79 99
Swansea 03/10/2018 14:50 25 1
Swansea 03/10/2018 14:50 51 37
Swansea 03/10/2018 15:20 63 90
Swansea 03/10/2018 15:20 61 100

 

[The_Doc_Man]

I did some research and think I may need to use “Recordset” but I don’t really understand how to apply it.

You are correct. (Or your research was correct, if you prefer.) When you write SQL, you need to understand that SQL implements your statement in whatever way seems best for the data set you have - as the ACE engine sees it. According to the set theory, there IS no next/previous record because the standard rules for SQL allow the engine to ACT as though everything occurred simultaneously. That means that it takes a lot of gyrations to write SQL to do what you want. Not impossible, but truly ugly.

You want to impose order on the set so that "Next" and "Previous" have meaning. Here is the basic problem. In the absence of some type of ordering syntax, a table is an unordered set of records, so "next record" and "previous record" are incredibly amorphous concepts. Therefore, anything you do must be based on a QUERY that contains an ORDER BY clause to enforce order on your records. Otherwise, ... chaos.

The typical way to do this with recordsets is to open your recordset, then inside a programming loop you read the first record for which it will not be possible to make the computation because it IS the first record. (I.e. nothing against which to compare.) Then store the critical values of that record, step to the next record, and compute your results based on stored values from the first record and current value from the second record. Compute what you need to compute. If your value to be stored goes to the 2nd record, you just tell the recordset you are going to .Edit and then modify the field you need to change, then do the .Update to write back (update) the 2nd record. Continue the loop until you reach the recordset.EOF=True condition on your loop.

But if you needed to do this update to the 1st record,... is there a way to do this backwards? I.e. if you have the values for the 1st and 2nd record, it doesn't matter in which order you read them - but if you need to write to the 1st record, you would do better to read the records in reverse order so that you can write the correct record. Which means that the "ORDER BY xxx" will include the keyword "DESC" - i.e. your query would include "ORDER BY xxx DESC" to make it read the records backwards.

Hope I didn't lose you on this. Read it over and see what sense it makes. I'm sure you will have other questions.

 

[CJ_London]

usual way is to use a subquery - databases have no concept of first/last/next/previous except with the application of an order so you need something to identify it specifically.

I don't understand your formula requirement - recommend you include how that works with the data provided

example of using a subquery would be something like

SELECT *, (SELECT TOP 1 Genrate FROM myTable AS S WHERE S.Location=T.Location AND S.DateTime=T.DateTime AND S.GenRate>=T.GenRate ORDER BY GenRate Desc) AS NextGenRate
FROM myTable AS T

But no idea if this is close to meet your needs.

new field called “SysD%”

strongly recommend you don't use none alpha numeric characters in field names

 

[caprice63]

Read it over and see what sense it makes.

Thank you. Your detailed guidance makes sense. I will try to implement it into code.

 

[caprice63]

Thank you.
That sounds interesting and may be simpler than using Recordset as I would not know where to start with Recordset.