INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"Because of this forum, I continue to WOW! my clients!"

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Modules (VBA Coding) Forum

Find and List the Missing Serial Numbers

Read More Threads Like This One

uncleG (TechnicalUser)

4 Dec 08 12:47

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 (Programmer)

4 Dec 08 20:12

Hi,

What application?

What database tool are you using?

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

SkipVought (Programmer)

4 Dec 08 20:20

Sorry, I thought I was in a different forum. blush

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

uncleG (TechnicalUser)

5 Dec 08 6:55

I am using Access 2003

MajP (TechnicalUser)

5 Dec 08 10:06

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 (TechnicalUser)

5 Dec 08 16:07

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 (TechnicalUser)

7 Dec 08 23:57

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

CODE

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"

CODE

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.

CODE

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

CODE

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

CODE

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 (TechnicalUser)

8 Dec 08 7:22

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 (TechnicalUser)

8 Dec 08 9:03

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

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

CODE

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

CODE

  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 (TechnicalUser)

8 Dec 08 10:23

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Modules (VBA Coding)

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Find and List the Missing Serial Numbers

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!