Compare the last two datapoints with 1

Arvidsson · Jul 19, 2014

Hello,

one more thread of mine today

I have this table:
-> tblTest

Code:

test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company A   |   01.01.2010  |   0,90
2 | Company A   |   02.01.2010  |   0,95
3 | Company A   |   03.01.2010  |   1,10
4 | Company A   |   05.01.2010  |   1,05
5 | Company B   |   01.01.2010  |   0,9
6 | Company B   |   02.01.2010  |   1,20
7 | Company B   |   03.01.2010  |   1,15
8 | Company B   |   05.01.2010  |   0,95
9 | Company B   |   07.01.2010  |   0,80

The important level is 1,00

Now I will, for each company, check:
- Is the value of ID-1 >1 AND the value of the ID-2 <1, THAN give me a 1
- Is the value of ID-1 <1 AND the value of the ID-2 >1, THAN give me a -1

In this example the result should look like this:

Code:

test_id | test_Name | test_Date | test_value | test_Code_result
-------------------------------------------------------------
1 | Company A   |   01.01.2010  |   0,90 | 
2 | Company A   |   02.01.2010  |   0,95 | 
3 | Company A   |   03.01.2010  |   1,10 | 
4 | Company A   |   05.01.2010  |   1,05 | 1
5 | Company B   |   01.01.2010  |   0,90 | 
6 | Company B   |   02.01.2010  |   1,20 | 
7 | Company B   |   03.01.2010  |   1,15 | 1
8 | Company B   |   05.01.2010  |   0,95 | 
9 | Company B   |   07.01.2010  |   0,80 | -1

Probably I have to carry out this in vba.
But i dont know how I can calculate across the datapoints (rows).

I would be very glad if someone could help me.

Thank you very much in advance!

pbaldy · Jul 19, 2014

Does this help?

http://allenbrowne.com/subquery-01.html#AnotherRecord

Arvidsson · Jul 19, 2014

pbaldy said:
Does this help?

http://allenbrowne.com/subquery-01.html#AnotherRecord

Thank you very much.

Unfortunately I used this

(SELECT TOP 1 Dupe.MeterValue ... Dupe.ReadDate < MeterReading.ReadDate ...

Approach in a other case. And because I have >200.000 datapoints the query runs 7 hours.

I solved the old case with:

Code:

Public Function GDerw(ByVal varSymbol As Variant, _
                      ByVal varValue As Variant) As Variant

  Static varSymbolAlt As Variant
  Static varValueAlt(2)   As Variant

  If varSymbol & vbNullChar = varSymbolAlt Then
    
    If varValue <> 0 Then
      varValueAlt(0) = varValueAlt(1)
      varValueAlt(1) = varValueAlt(2)
      varValueAlt(2) = varValue
    
    End If

  Else
    varSymbolAlt = varSymbol & vbNullChar
    varValueAlt(0) = Null
    varValueAlt(1) = Null
    varValueAlt(2) = varValue
    
  End If

    GDerw = varValueAlt(2)

End Function

Maybe this is a good base for a new code.

vbaInet · Jul 19, 2014

Arvidsson said:
Now I will, for each company, check:
- Is the value of ID-1 >1 AND the value of the ID-2 <1, THAN give me a 1
- Is the value of ID-1 <1 AND the value of the ID-2 >1, THAN give me a -1

I'm having difficulty understand these equations. I'm sure Paul does anyway

Arvidsson · Jul 19, 2014

Code:

test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company A   |   01.01.2010  |   0,90
2 | Company A   |   02.01.2010  |   0,95
3 | Company A   |   03.01.2010  |   1,10
4 | Company A   |   05.01.2010  |   1,05
5 | Company B   |   01.01.2010  |   0,9
6 | Company B   |   02.01.2010  |   1,20
7 | Company B   |   03.01.2010  |   1,15
8 | Company B   |   05.01.2010  |   0,95
9 | Company B   |   07.01.2010  |   0,80

The companies should be checked:
Company A:
ID 1: nothing, because i cant check ID-1 and ID-2 (no datas for this company)
ID 2: nothing, because i cant check ID-1 and ID-2 (no datas for this company)
ID 3: Value of ID-1: 0,95 = <1 Value of ID-2: 0,90 = <1
---> The rules for 1 or -1 are not fulfilled.
ID 4: Value of ID-1: 1,10 = >1 Value of ID-2: 0,95 = <1
---> The rule for 1 is fulfilled.
Company B:
ID 5: nothing, because i cant check ID-1 and ID-2 (no datas for this company)
ID 6: nothing, because i cant check ID-1 and ID-2 (no datas for this company)
ID 7: Value of ID-1: 1,20 = >1 Value of ID-2: 0,90 = <1
---> The rule for 1 is fulfilled.
ID 8: Value of ID-1: 1,15 = >1 Value of ID-2: 1,20 = >1
---> The rules for 1 or -1 are not fulfilled.
ID 9: Value of ID-1: 0,95 = <1 Value of ID-2: 1,15 = >1
---> The rule for -1 is fulfilled.

I hope this helps to understand my intention.

vbaInet · Jul 19, 2014

I still don't understand because IDs 1 to 4 are Company A and the rest are Company B. Only 2 companies.

Arvidsson · Jul 19, 2014

There are much more companies, this is only a simple example.

One ID for one day.
And only the IDs of one company should be the basis for the calculation.
E.g. dont compare on ID 5 the ID 4 and 3, because ID 5 is Company 5 and 4 & 3 are Company A.

I tried to write what should checked in each "row" / "datapoint".

vbaInet · Jul 19, 2014

So is it actually supposed to be like this?

Arvidsson said:

Code:

test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company [COLOR="Red"]A[/COLOR]   |   01.01.2010  |   0,90
2 | Company [COLOR="red"]B   [/COLOR]|   02.01.2010  |   0,95
3 | Company [COLOR="red"]C   [/COLOR]|   03.01.2010  |   1,10
4 | Company [COLOR="red"]D   [/COLOR]|   05.01.2010  |   1,05
5 | Company [COLOR="red"]E   [/COLOR]|   01.01.2010  |   0,9
6 | Company [COLOR="red"]F   [/COLOR]|   02.01.2010  |   1,20
7 | Company [COLOR="red"]G   [/COLOR]|   03.01.2010  |   1,15
8 | Company [COLOR="red"]H   [/COLOR]|   05.01.2010  |   0,95
9 | Company [COLOR="red"]I   [/COLOR]|   07.01.2010  |   0,80

Arvidsson · Jul 19, 2014

No, like this:

Code:

test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company A   |   01.01.2010  |   0,90
2 | Company A   |   02.01.2010  |   0,95
3 | Company A   |   03.01.2010  |   1,10
4 | Company A   |   05.01.2010  |   1,05
5 | Company B   |   01.01.2010  |   0,9
6 | Company B   |   02.01.2010  |   1,20
7 | Company B   |   03.01.2010  |   1,15
8 | Company B   |   05.01.2010  |   0,95
9 | Company B   |   07.01.2010  |   0,80
10 | Company C   |   01.01.2010  |   0,9
11 | Company C   |   02.01.2010  |   1,20
12 | Company C   |   03.01.2010  |   1,15
13 | Company C   |   05.01.2010  |   0,95
14 | Company D   |   07.01.2010  |   0,80
15 | Company D   |   01.01.2010  |   0,9
16 | Company D   |   02.01.2010  |   1,20
17 | Company D   |   03.01.2010  |   1,15
18 | Company D   |   05.01.2010  |   0,95
19 | Company E   |   07.01.2010  |   0,80

...

Fore each companies are some dates available.

vbaInet · Jul 19, 2014

Oh you're using the ID as the index. Alright, I completely understand now. Is your table/query going to be sorted by the ID?

Arvidsson · Jul 19, 2014

Yes it the index.

It is sorted by test_Symbol and test_date.

The ID will be created after this sorting:

Code:

Public Function ID_Laufende_Nummer()
  DBEngine.SetOption dbMaxLocksPerFile, 300000
  Dim sql As String
  Dim RS As DAO.Recordset
  Dim i As Long
    i = 1
    sql = "SELECT * FROM tblTest ORDER BY test_Symbol, Test_date;"
    Set RS = CurrentDb.OpenRecordset(sql)
    Do Until RS.EOF
    RS.Edit
      RS.Fields("test_ID") = i
      i = i + 1
      RS.Update
      RS.MoveNext
    Loop
    RS.Close
    Set RS = Nothing
End Function

I can not use an AutoValue because the table will be extended an than I get some "ID gaps" in the companies.

CJ_London · Jul 19, 2014

You'll need 3 aliased tables, I'll call them A, B and C

Referencing your example, A will prove the 'reporting' row, B is the ID-1 and C ID-2

Left join between A and B and A and C on Company to ensure all rows are returned.

do your calculation based on comparing B.result with C.result

then In the sql viewer, modify the join to include B.ID=A.ID-1 and C.ID=A.ID-2 - note you can't do this from the query window which is why I suggest leave it until last

edit - and you can't just put this in as a criteria otherwise all rows will not be returned

vbaInet · Jul 19, 2014

Ok, since all the IDs will always be an increment of 1 you can create a LEFT JOIN with the same table using (ID-1) and (ID-2) to join three of them. Use the value from the two tables to compare against the original table.

Edit: I see CJ was here before me.

Arvidsson · Jul 19, 2014

Ok, thank you very much. I will try it

Arvidsson · Jul 19, 2014

I think I got it.

But it is very slow.
The functions I have for e.g. the calculation of a moving average works much faster.

Isnt it possible to creat a function in this case, too?

I would do it by myself, but I dont know how I can caluclate with different rows.
Maybe someone has an example?

Thank you very much!

vbaInet · Jul 19, 2014

Let's see the SQL statement.

Arvidsson · Jul 19, 2014

I made a mistake :banghead:, now it works significant faster.

Nevertheless, I am very interested to calculate tasks of this type in VBA.

Do you know how it works?

vbaInet · Jul 19, 2014

The VBA (or more specifically Recordset) option won't be as fast as JOINS in general. And in your case you will have to move back and forth.

There's no VBA version. All you need to do is learn the basics of VBA, learn how to use recordsets and you'll know how to do.

Compare the last two datapoints with 1

Arvidsson

Registered User.

pbaldy

Wino Moderator

Arvidsson

Registered User.

vbaInet

AWF VIP

Arvidsson

Registered User.

vbaInet

AWF VIP

Arvidsson

Registered User.

vbaInet

AWF VIP

Arvidsson

Registered User.

vbaInet

AWF VIP

Arvidsson

Registered User.

CJ_London

Super Moderator

vbaInet

AWF VIP

Arvidsson

Registered User.

Arvidsson

Registered User.

vbaInet

AWF VIP

Arvidsson

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread