Hello!
Firstly let me introduce myself. I am an undergraduate student in Earth Sciences working with Access since a few months as part of my Thesis.As I have ~3000 boreholes with in total around 50k layers I can't rely on manually entering data but have to automate everything. I m working boreholes which contain distinctive layers. These layers can be lying continuous on top of each other or lie discontinuous on one another (which is called a 'boundary'). so for example a table may look like this:
Borehole |
Boundary |
Sequence |
x |
no |
|
x |
Yes |
|
x |
no |
|
x |
no |
|
x |
Yes |
|
x |
no |
|
x |
no |
|
y |
no |
|
y |
no |
|
y |
Yes |
|
y |
no |
|
y |
no |
|
y |
no |
|
z |
no |
|
z |
Yes |
|
z |
no |
|
z |
Yes |
|
z |
Yes |
|
z |
no |
|
Now, what I want the query to do is create sequential numbering in the third column, where every new borehole the value is reset to '1' and every boundary the value is increased by '1'. i.e.:
Borehole |
Boundary |
Sequence |
z |
no |
1 |
z |
yes |
2 |
z |
no |
2 |
z |
yes |
3 |
z |
no |
3 |
z |
no |
3 |
z |
yes |
4 |
z |
no |
4 |
What I have right now is wrong as it doesn't (clearly) updates everything at once.
Code:
IIf([Sterksel_Grainsizes]![nr]=[sterksel_grainsizes_1]![nr];IIf([Sterksel_Sequences]![Sequence_boundary]="boundary";[Sterksel_Sequences]![sequence_number]+1;[Sterksel_sequences]![sequence_number]);1)
What I need is a query that looks at the row above before calculating a value. My question is if that is even possible within one or more queries.
Many thanks in advance!