Update qry - keep the smallest number

[editolis]

Hi All,
Need a help with a UPD qry.
I Have 3 fields.
The names are: ISSN1, ISSNX and ISSN3.
I want an update qry to delete the 2 from the 3 fields and make them null.
The rule 1 is to keep the smallest number from the 3 fields.
So let’s say we have ISSN1= 1,25, ISSNX =2,26, ISSN2=2,15 then we keep the number from the ISSN1 who is the smallest and we delete the ISSNX and the ISSN2 and we make them null.
The rule 2 is that if the 3 fields are exactly the same then the field ISSNX is the winner and we keep it.
The rule 3 is that if the fields ISSN1 and ISSNX are exactly the same then the winner is ISSNX and we keep it. (Of course the fields ISSN1 and ISSNX they have smallest numbers from the ISSN2)
The rule 4 is that if the fields ISSN2 and ISSNX are exactly the same then the winner is ISSNX and we keep it. (Of course the fields ISSN2 and ISSNX they have smallest numbers from the ISSN1)
And finally the rule 5 is that if the fields ISSN1 and ISSN2 are exactly the same then the winner is ISSN1 and we keep it. (Of course the fields ISSN1 and ISSN2 they have smallest numbers from the ISSNX)
The database with the table included.
The first 5 fields I have already fix them “by hand”.
Need someone to help me building the Update qry.
Thank you in advance.

 

[WayneRyan]

Editolis,

Something like this:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Select * From tbl1 Where ID >= 6 Order By ID")
While Not rst.EOF And Not rst.BOF
   rst.Edit
   ' The rule 1 is to keep the smallest number from the 3 fields.
   ' So let’s say we have ISSN1= 1,25, ISSNX =2,26, ISSN2=2,15 then 
   ' we keep the number from the ISSN1 who is the smallest and we 
   ' delete the ISSNX and the ISSN2 and we make them null.
   If rst!ISSN1 < rst!ISSN2 And rst!ISSN1 < rst!ISSNX Then
      rst!ISSN2 = ''
      rst!ISSNX = ''
   ElseIf rst!ISSN2 < rst!ISSN1 And rst!ISSN2 < rst!ISSNX Then
      rst!ISSN1 = ''
      rst!ISSNX = ''
   ElseIf rst!ISSNX < rst!ISSN1 And rst!ISSNX < rst!ISSN2 Then
      rst!ISSN1 = ''
      rst!ISSN2 = ''
   ' The rule 2 is that if the 3 fields are exactly the same then the field 
   ' ISSNX is the winner and we keep it.
   ElseIf rst!ISSNX = rst!ISSN1 And rst!ISSNX = rst!ISSN2 Then
      rst!ISSN1 = ''
      rst!ISSN2 = ''
   ' The rule 3 is that if the fields ISSN1 and ISSNX are exactly the 
   ' same then the winner is ISSNX and we keep it. (Of course the fields 
   ' ISSN1 and ISSNX they have smallest numbers from the ISSN2)
   ElseIf rst!ISSNX = rst!ISSN1 Then
      rst!ISSN1 = ''
      rst!ISSN2 = ''
   ' The rule 4 is that if the fields ISSN2 and ISSNX are exactly the same 
   ' then the winner is ISSNX and we keep it. (Of course the fields ISSN2 
   ' and ISSNX they have smallest numbers from the ISSN1)
   ElseIf rst!ISSNX = rst!ISSN2 Then
      rst!ISSN1 = ''
      rst!ISSN2 = ''
   ' And finally the rule 5 is that if the fields ISSN1 and ISSN2 are exactly 
   ' the same then the winner is ISSN1 and we keep it. (Of course the fields 
   ' ISSN1 and ISSN2 they have smallest numbers from the ISSNX)
   ElseIf rst!ISSN1 = rst!ISSN2 Then
      rst!ISSNX = ''
      rst!ISSN2 = ''
   End If
   rst.Update
   rst.MoveNext
   Wend

hth,
Wayne

 

[editolis]

Thank you very much Wayne.

 

[WayneRyan]

E,

Oops,

Change:

rst!ISSN2 = ''

To

rst!ISSN2 = ""

Wayne