How to subtract values from two fields in two consecutive rows in Access table?

[nelpet]

Hi,

I have a table with unique number of vehicle and the shift it works in. There are columns for number of vehicle, shift, starting km and ending km. I want to subtract or only compare the values form the columns ending km and starting km from the consecutive rows depending on the shift. I ordered the table by date, number of the vehicle and shift, but I don't know how to compare the fields starting km from one row (the first row for example) and the ending km field from the row after it ( i.e. the second row) in this case?
Can anybody help me please?

Thanks in advance.

 

[vbaInet]

Can you show us what you're trying to achieve on a spreadsheet.

 

[nelpet]

THIS IS THE EXAMPLE:


So I like to ckeck whether Krajkm for 04-02 (the ending km for second shift of line number 4) is smaller than pockm for 04-03 (the starting km for third shift of the same line number i.e. number 4) i.e. to check and compare cosecutive values for starting and ending km.
Thanks

 

[DavidAtWork]

You need to use some vba to inspect a recordset of values from this table, the sql would have to order it first by vehicle, then by date, then by shift. This way the records will be coming in a consistent order allowing you to compare the values of POCKM and KRAJKM.
As you cycle the recordset, you can use variables to hold values for the fields above such as thisPOCKM, thisKRAJKM, prevPOCKM, prevKRAJKM.
The first record set values for thisPOCKM, thisKRAJKM, then assign these values to prevPOCKM, prevKRAJKM by saying
prevPOCKM = thisPOCKM, prevKRAJKM = thisKRAJKM, then move to next record
again assign values to thisPOCKM, thisKRAJKM and now you can compare
thisPOCKM to prevKRAJKM to see if there is any difference and perform some action.
You can just keep repeating this until you're at the EOF for this vehicle. You can track the vehicle by checking the vehicle id to see if it changes, when it does you need skip the comparison/checking for the first record
What I've written here is very simplified as there are many ways to do this.
David

 

[vbaInet]

So I like to ckeck whether Krajkm for 04-02 (the ending km for second shift of line number 4) is smaller than pockm for 04-03 (the starting km for third shift of the same line number i.e. number 4) i.e. to check and compare cosecutive values for starting and ending km.
Thanks

What is the Data Type of SL.?

 

[nelpet]

The data types of krajkm and pockm are Number. So, in the example above I want to ckeck whether krajkm for 04-01 is less or equal than pockm of 04-02. SL. is of type text.

 

[vbaInet]

So "04-" is set in the Format property of the field?

 

[nelpet]

No, there is nothing defined in the format property of the field in the access database for the field SL.Only I set the data type to be Text.

 

[vbaInet]

Alright, my bad. You clearly mentioned that in your other post.

Is it actually 04-01 or 04-1? In your screenshot it's 04-1 but in your last few posts you've been writing 04-01.

 

[nelpet]

That's not important. I just took it as an example. It could be 07-01 and 07-02 and 07-03 (so three shifts of line number 7), or whichever other number combination. What is important is that I want to compare shifts from the same line or same vehicle number.

 

[vbaInet]

If you feel that's not important then I can't help you. Perhaps someone who knows how to do it without the information asked can help.

One thing you fail to note is that you're getting free advice from experts on this forum.

 

[nelpet]

I didn't mean to offend anybody. Sorry for that. Thanks for your try anyway.

 

[vbaInet]

We have a solution in our heads which is why we ask these questions. We're the ones providing the solutions so every question we ask is relevant in providing a working solution.

So, I need to know whether it's in this format "04-01" or in this format "04-1". Let me know and I'll tell you how to progress.

 

[nelpet]

It's in format "04-01"

 

[vbaInet]

I thought I had replied to your thread, but I obviously haven't. My apologies!

Here's what you do:

1. Create a query to include the following fields:

Query:
SL    |    Pockm    |    Krajkm    |    RightSL: Val(mid([pockm], instr(1, [pockm], "-") + 1))

Notice the new field, RightSL that is an Expression.
2. Give the query a meaningful name and Order By RightSL in Ascending Order
3. Create another query and base it on your table with all the fields you had in your image present, then for the calculation you can use one of the following:

[pockm] < Nz(DLookup("pockm", "[COLOR=Red]QueryName[/COLOR]", "[RightSL] > " & Val(mid([pockm], instr(1, [pockm], "-") + 1))), 0)


[pockm] < (SELECT Q.[pockm] FROM [COLOR=Red]QueryName[/COLOR] AS Q WHERE Q.[RightSL]  > Val(mid([COLOR=Red]TableName[/COLOR].[pockm], instr(1, [COLOR=Red]TableName[/COLOR].[pockm], "-") + 1)))

Amend the bits in red and note that the second one will run faster. Use the same concept for krajkm.