Populate 2 records based on a comparison of data

[necapa82]

I have an Access 2000 database in which I need to update both records in a pairing, based on the difference in the value of a quantity field.

This is an example of the pertinent data in the table.

Pairing Individual Quantity Status
AB A 15
AB B 13

CD C 13
CD D 15

EF E 14
EF F 14

There will always be 2 individuals per pairing.
Each individual will have a value in the quantity field.

I need to populate a status field for both individuals, based on the difference between the quantity values of each of the individuals in the pairing. The actual difference is not relevant for this status code, just which individual’s quantity is More than, Less than or Equal to the other individual’s quantity. The codes could be +1, -1 or 0 if I need to us them in other calculations. The results I need would be similar to this:

Pairing Individual Quantity Status
AB A 15 M or +1
AB B 13 L or -1

CD C 13 L or -1
CD D 15 M or +1

EF E 14 E or 0
EF F 14 E or 0

How can I do this in Access 2000?

Thanks,

[ADezii]

I have an Access 2000 database in which I need to update both records in a pairing, based on the difference in the value of a quantity field.

This is an example of the pertinent data in the table.

Pairing Individual Quantity Status
AB A 15
AB B 13

CD C 13
CD D 15

EF E 14
EF F 14

There will always be 2 individuals per pairing.
Each individual will have a value in the quantity field.

I need to populate a status field for both individuals, based on the difference between the quantity values of each of the individuals in the pairing. The actual difference is not relevant for this status code, just which individual’s quantity is More than, Less than or Equal to the other individual’s quantity. The codes could be +1, -1 or 0 if I need to us them in other calculations. The results I need would be similar to this:

Pairing Individual Quantity Status
AB A 15 M or +1
AB B 13 L or -1

CD C 13 L or -1
CD D 15 M or +1

EF E 14 E or 0
EF F 14 E or 0

How can I do this in Access 2000?

Thanks,

Create a Query consisting of the [Pairing], [Individual], and [Quantity] Fields. Create a Calculated Field called Status which will call the fReturnStatus2([Quantity]) Function and return the appropriate return value for each matching pair. All the code has been centralized within the Function for demo purposes only. This is not the most efficient way to arrive at the answer especially with the Arrays being populated for each Record. Good Luck.

Calculated Field ==> Status: fReturnStatus2([Quantity])

Public Function fReturnStatus2(MyQuantity As Integer)
Dim MyDB As Database, MyRS As Recordset
Static intRecCounter As Integer
Dim intNumberOfRecords As Integer
Dim T As Integer
 
intRecCounter = intRecCounter + 1   'Current Record Number
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblPairings", dbOpenDynaset)
 
MyRS.MoveLast: MyRS.MoveFirst
 
intNumberOfRecords = MyRS.RecordCount
 
ReDim arrFirst(1 To intNumberOfRecords) As Integer
ReDim arrSecond(1 To intNumberOfRecords) As Integer
 
Do While Not MyRS.EOF
  T = T + 1
  arrFirst(T) = MyRS![Quantity]
  arrSecond(T) = arrFirst(T)
  MyRS.MoveNext
Loop
 
MyRS.Close
 
If intRecCounter < intNumberOfRecords Then
  If intRecCounter Mod 2 <> 0 Then  'ODD Record beginning of Pair, compare DOWN
    If arrFirst(intRecCounter) > arrSecond(intRecCounter + 1) Then
      fReturnStatus2 = "M"
    ElseIf arrFirst(intRecCounter) < arrSecond(intRecCounter + 1) Then
      fReturnStatus2 = "L"
    Else
      fReturnStatus2 = "E"
    End If
  Else      'Even Record end of Pair, compare UP
    If arrSecond(intRecCounter) > arrFirst(intRecCounter - 1) Then
      fReturnStatus2 = "M"
    ElseIf arrSecond(intRecCounter) < arrFirst(intRecCounter - 1) Then
      fReturnStatus2 = "L"
    Else
      fReturnStatus2 = "E"
    End If
  End If
End If
 
If intRecCounter = intNumberOfRecords Then intRecCounter = 0
End Function

Sample Output:

Pairing    Individual   Quantity     Status
AB        A          15           M
AB        B          13           L
CD        C          13           L
CD        D          15           M
EF        E          14           E
EF        F          14           E
GH        G          19           L
GH        H          21           M
IJ        I          47           M
IJ        J          33    
 

[necapa82]

Thank you very much.

It's a great help to me (and the project)!!!

[Rabbit]

ADezii, you can call that function from a query? Whenever I try it I get an error, unknown function.

[ADezii]

ADezii, you can call that function from a query? Whenever I try it I get an error, unknown function.

It must be declared as Public in a Standard Module in order to accessed from the Query Grid.

[ADezii]

Thank you very much.

It's a great help to me (and the project)!!!

Glad I could help, it really had me stumped for awhile.

[Rabbit]

It must be declared as Public in a Standard Module in order to accessed from the Query Grid.

That's what I do but it doesn't work.

[Rabbit]

Never mind, I'm an idiot. I didn't declare it as public, coulda sworn I did though.

[NeoPa]

I would suggest using SQL in a format similar to :

UPDATE YourTable AS T INNER JOIN
       (SELECT Pairing,
               Min([Quantity]) AS MinQ,
               Max([Quantity]) AS MaxQ
        GROUP BY Pairing) AS subP
    ON T.Pairing=subP.Pairing
SET T.Status=IIf(subP.MinQ=subP.MaxQ,0,IIf(T.Quantity>subP.MinQ,1,-1))

[ADezii]

I would suggest using SQL in a format similar to :

UPDATE YourTable AS T INNER JOIN
       (SELECT Pairing,
               Min([Quantity]) AS MinQ,
               Max([Quantity]) AS MaxQ
        GROUP BY Pairing) AS subP
    ON T.Pairing=subP.Pairing
SET T.Status=IIf(subP.MinQ=subP.MaxQ,0,IIf(T.Quantity>subP.MinQ,1,-1))

Interesting solution, NeoPa. Just for curiosity, has it been tested on actual data, and have the results been accurate and consistent? It's just my endless curiosity.