Find and List the Missing Serial Numbers 1

[uncleG]

Hi All, Once again I am stumped. Yes looking for direction.
Legacy data and a few customers prevents using an LI for Serial Numbers

1) I would like to create a module or procedure to tell me which serial numbers are missing.
This is this origin of the SN List which is passed from Labview via ODBC, the SN can be either Text or LI.

qryFinalSnInList
SELECT DISTINCT qryDataRaw2L.final_sn
FROM qryDataRaw2L
WHERE (((qryDataRaw2L.final_sn) Between [Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text362] And
[Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text363]))
ORDER BY qryDataRaw2L.final_sn;

[Text362] is the Min in List
[Text363] is the Max in List
[final_sn] is a text field


2) I would also like to be able to pass both the Pass and Fail lists formated as comma seperated into a memo field for later export such as:

qryFinalSnPass
SELECT DISTINCT qryDataRaw2L.final_sn, qryDataRaw2L.PassFail
FROM qryDataRaw2L
WHERE (((qryDataRaw2L.final_sn) Between [Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text362] And
[Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text363]) AND ((qryDataRaw2L.PassFail)="Pass"))
ORDER BY qryDataRaw2L.final_sn;

Desired Output: [snPassList] = 94352-94362, 94366-94404, 94406-94527


qryFinalSnFail
SELECT DISTINCT qryDataRaw2L.final_sn, qryDataRaw2L.PassFail
FROM qryDataRaw2L
WHERE (((qryDataRaw2L.final_sn) Between [Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text362] And

[Forms]![frmDataRaw0Limit]![frmDataRaw2L]![Text363]) AND ((qryDataRaw2L.PassFail)="Fail"))
ORDER BY qryDataRaw2L.final_sn;

Desired Output: [snFailList] = 94363-94365, 94405

Thanks Again,
UncleG

 

[SkipVought]

Hi,

What application?

What database tool are you using?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Sorry, I thought I was in a different forum.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[uncleG]

I am using Access 2003

 

[MajP]

What do you mean by missing serial? Can you explain the desired output? Not sure what that means.
94352-94362, 94366-94404, 94406-94527

Are you saying you have serials like

94352
94353
94357

and you want the missing serial to be
94354,94355, 94356
but write the output like
94354 - 95356

Or does 94354,94355, 94356 exist in another list.

If the values do not exist you will have to write code. If the values exist, but are not returned by your query you can use Sql.

If the sequence is not numerical the code get more complicated

ab123
ab124
ab126
ac567
ac569

The missing serials above would be based on a complicated buisness rule.

 

[uncleG]

Thanks for the response MajP,
Some answers to your questions, maybe?

The complete set of numbers do not exist in another list, the list would need to be built in code first using the min and the max on the form. At this point I am trying to verify that all products were indeed tested.(no missing serial numbers unless the product failed in an earlier test, who's results are on paper.)
Next the existing SN's would need to be compared to the number range and a list produced of the missing numbers.
(this would tell me who is missing)Counting records doesn't work because sometimes the product is tested 2, 3 or 6 times times for any number of reasons.
At this stage it would be fine as a simple column listing the individuals not present in the group. This will occur as part of the batch review going forward. I continue to refer to the SN's as numbers but they are text.

There are both legacy records and customer supplied serial numbers which are in fact text and numbers mixed, which also has the potential to create duplicate SN's however they are isolated via a Work Order and Batch Number. These records exist in several old programs and we are attempting to bring them out into one table, and there have been some dupicates but not of the same product. I am considering using a number field to reassign a key to provide structure and use the old SN (Text) as a name for cross reference. Once out I can normalize the structure.

The Dash Comma Format Thing:
Just something I have been kicking around.
Find the numbers missing in a group and produce the following output format as opposed to listing each individual.
The output Like "Desired Output: [snPassList] = 94352-94362, 94366-94404, 94406-94527" is the format we use on a certificate which is shipped with the product, looks like a stupid diploma. At this point in time the SN list is complete but the products which failed inspection are not in the list so there are gaps which is why they use the comma and dashes. I've played with a few things here but have gotten nowhere and deleted those attempts. By the time I get through this one I should have a good understanding of looping.
Thanks for your interest,
UncleG

 

[MajP]

Your example shows numeric values not alpha numeric values. If they are numeric values saved as text the solution is pretty easy. If they are alpha numeric values, there is no generic algorithm that anyone can write. Because you would have to sequence the values based on your buisness rules. For example
Ab123xy
What is the next value in the sequence?
Ab123xz or Ab124xy or ac123xy or bb123xy or something else
Depends on your rules. This will really complicate the problem. It is doable, but will be very complicated.

If I had numeric values that had a natural sequence. I would build a function that returns the sequence as an array. This gives you the flexible to print the array, or save it to a table.

here is my data of items tested

ID	final_sn
1	94353
2	94351
3	94351
4	94353
5	94357
6	94357
7	94369
8	94366
9	94367
10	94363
11	94369
12	94370
13	94371
14	94370
15	94372

I sort them and select unique values in "qrySNList"

final_sn
94351
94353
94357
94363
94366
94367
94369
94370
94371
94372

then build a function where I can pass in the min and max range. These values can come from a form's fields. I also pass in the name of the table/query and the field name.

Public Function getMissingSN(strDomain As String, strFld As String, minVal As Variant, maxVal As Variant) As Long()
  Dim rs As DAO.Recordset
  Dim missingVal As Long
  Dim aMissingVals() As Long
  Dim intcounter As Integer
  Set rs = CurrentDb.OpenRecordset(strDomain, dbOpenDynaset)
  ReDim aMissingVals((maxVal - minVal) + 1)
  For missingVal = minVal To maxVal
    rs.FindFirst strFld & " = '" & missingVal & " '"
    If rs.NoMatch Then
      aMissingVals(intcounter) = missingVal
      intcounter = intcounter + 1
    End If
  Next missingVal
  If aMissingVals(intcounter) = 0 Then
    intcounter = intcounter - 1
  End If
  ReDim Preserve aMissingVals(intcounter)

  getMissingSN = aMissingVals
End Function

I test this function

Public Sub testMissing()
  Dim vals() As Long
  Dim intcounter As Integer
  vals = getMissingSN("qrySNList", "final_sn", "94354", "94370")
  For intcounter = LBound(vals) To UBound(vals)
      Debug.Print vals(intcounter)
    Next intcounter

End Sub

You could save these values to a table. The result is

 94354 
 94355 
 94356 
 94358 
 94359 
 94360 
 94361 
 94362 
 94364 
 94365 
 94368

Changing this into the "beginValue - endValue" format will be a pain. It is doable, but will take some work. You have to read through the array checking to see if the value is more than one greater than the previous value. If it is you can concatenate it as the "endValue". If not you have to check the next value. Until you find a value more than 1 greater than the previous or you reach the end of the array. If you find an end value then the next value is you "beginValue".

 

[uncleG]

Thanks MajP,
This logic will work going forward, the function is exactly what I was looking for. As far as a Business Rule goes it had been a free for all, but I have been tasked with changing that.
Thanks for your help,
UncleG

 

[MajP]

If you are using an alpha numeric you would modify the code

  For missingVal = minVal To maxVal
    rs.FindFirst strFld & " = '" & missingVal & " '"
    If rs.NoMatch Then
      aMissingVals(intcounter) = missingVal
      intcounter = intcounter + 1
    End If
  Next missingVal

to something like

dim firstPass as boolean
firstPass = true
missingVal = minVal
do
  if not firstPass then
    missingVal = nextVal(missingVal)
  end if
  rs.FindFirst strFld & " = '" & missingVal & " '"
    If rs.NoMatch Then
      aMissingVals(intcounter) = missingVal
      intcounter = intcounter + 1
   End If 
   firstPass = false 
loop until maxVal = missingVal

  public function nextVal(currentVal as string) as string
    ' need some code here to iterate your series
    ' based on your rules
    ' so if you pass in 'abc123xy'
    ' it will return the next value like 'abc123xz'
    nextVal =
  end function

 

[uncleG]

Hi MajP,
I will play with the alpha numeric as well later in the week after we settle (hopefully) on a new format.
Thanks Again and have a Happy Holiday season,
UncleG