By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

Populate 2 records based on a comparison of data

P: 3
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,
Feb 26 '07 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,597
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.
Expand|Select|Wrap|Line Numbers
  1. Calculated Field ==> Status: fReturnStatus2([Quantity])
Expand|Select|Wrap|Line Numbers
  1. Public Function fReturnStatus2(MyQuantity As Integer)
  2. Dim MyDB As Database, MyRS As Recordset
  3. Static intRecCounter As Integer
  4. Dim intNumberOfRecords As Integer
  5. Dim T As Integer
  6.  
  7. intRecCounter = intRecCounter + 1   'Current Record Number
  8. Set MyDB = CurrentDb()
  9. Set MyRS = MyDB.OpenRecordset("tblPairings", dbOpenDynaset)
  10.  
  11. MyRS.MoveLast: MyRS.MoveFirst
  12.  
  13. intNumberOfRecords = MyRS.RecordCount
  14.  
  15. ReDim arrFirst(1 To intNumberOfRecords) As Integer
  16. ReDim arrSecond(1 To intNumberOfRecords) As Integer
  17.  
  18. Do While Not MyRS.EOF
  19.   T = T + 1
  20.   arrFirst(T) = MyRS![Quantity]
  21.   arrSecond(T) = arrFirst(T)
  22.   MyRS.MoveNext
  23. Loop
  24.  
  25. MyRS.Close
  26.  
  27. If intRecCounter < intNumberOfRecords Then
  28.   If intRecCounter Mod 2 <> 0 Then  'ODD Record beginning of Pair, compare DOWN
  29.     If arrFirst(intRecCounter) > arrSecond(intRecCounter + 1) Then
  30.       fReturnStatus2 = "M"
  31.     ElseIf arrFirst(intRecCounter) < arrSecond(intRecCounter + 1) Then
  32.       fReturnStatus2 = "L"
  33.     Else
  34.       fReturnStatus2 = "E"
  35.     End If
  36.   Else      'Even Record end of Pair, compare UP
  37.     If arrSecond(intRecCounter) > arrFirst(intRecCounter - 1) Then
  38.       fReturnStatus2 = "M"
  39.     ElseIf arrSecond(intRecCounter) < arrFirst(intRecCounter - 1) Then
  40.       fReturnStatus2 = "L"
  41.     Else
  42.       fReturnStatus2 = "E"
  43.     End If
  44.   End If
  45. End If
  46.  
  47. If intRecCounter = intNumberOfRecords Then intRecCounter = 0
  48. End Function
Sample Output:
Expand|Select|Wrap|Line Numbers
  1. Pairing    Individual   Quantity     Status
  2. AB        A          15           M
  3. AB        B          13           L
  4. CD        C          13           L
  5. CD        D          15           M
  6. EF        E          14           E
  7. EF        F          14           E
  8. GH        G          19           L
  9. GH        H          21           M
  10. IJ        I          47           M
  11. IJ        J          33    
  12.  
Feb 27 '07 #2

P: 3
Thank you very much.

It's a great help to me (and the project)!!!
Feb 27 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
ADezii, you can call that function from a query? Whenever I try it I get an error, unknown function.
Feb 27 '07 #4

ADezii
Expert 5K+
P: 8,597
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.
Feb 27 '07 #5

ADezii
Expert 5K+
P: 8,597
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.
Feb 27 '07 #6

Rabbit
Expert Mod 10K+
P: 12,315
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.
Feb 27 '07 #7

Rabbit
Expert Mod 10K+
P: 12,315
Never mind, I'm an idiot. I didn't declare it as public, coulda sworn I did though.
Feb 27 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
I would suggest using SQL in a format similar to :
Expand|Select|Wrap|Line Numbers
  1. UPDATE YourTable AS T INNER JOIN
  2.        (SELECT Pairing,
  3.                Min([Quantity]) AS MinQ,
  4.                Max([Quantity]) AS MaxQ
  5.         GROUP BY Pairing) AS subP
  6.     ON T.Pairing=subP.Pairing
  7. SET T.Status=IIf(subP.MinQ=subP.MaxQ,0,IIf(T.Quantity>subP.MinQ,1,-1))
Feb 28 '07 #9

ADezii
Expert 5K+
P: 8,597
I would suggest using SQL in a format similar to :
Expand|Select|Wrap|Line Numbers
  1. UPDATE YourTable AS T INNER JOIN
  2.        (SELECT Pairing,
  3.                Min([Quantity]) AS MinQ,
  4.                Max([Quantity]) AS MaxQ
  5.         GROUP BY Pairing) AS subP
  6.     ON T.Pairing=subP.Pairing
  7. 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.
Feb 28 '07 #10

NeoPa
Expert Mod 15k+
P: 31,186
That's a perfectly valid question ADezii.
No, I did not test this as it was tailored to fit the OP's specific environment. I'm afraid I don't have the time available to test all my answers to that level, although I'm confident that it will hold together pretty well. The concepts are all tried and tested (unless I've overlooked something of course).
If you do find any problems with it then I'll certainly revisit it and I've sometimes been asked to explain why things work, which I'm happy to do when asked.
Feb 28 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.