Results 1 to 14 of 14
  1. #1
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82

    Checking Table for Gaps in a sequence of Numbers

    I am in the process of creating a databases to track various cables that are run for a television station. All of our cables are given an alphanumeric label based on how the cable is categorized by the function of the cable. For example if we run a Cat6 cable for a network connection, the cable would be labeled NET1234 and a cable that we run for carrying a high definition video signal would be labeled HDV1234.

    We will be deleting the records of the cables that are being removed as equipment is decommissioned and removed. This will result in gaps our records of the various categories of cable label. We would go from having a list of cables like:

    NET1234


    NET1235
    NET1236
    NET1237
    NET1238

    to a list of cable labels like:

    NET1234
    NET1236
    NET1237

    I am trying make a query that would find the gaps in the cable labels so that we can reuse the alphanumeric labels. We have 13 categories of wire that we will need to search through to find gaps. After the query is run we would get something like:

    NET1235
    NET1238

    The data for the alphanumeric labels resides on a field labeled wireNumber on a table called wireInfo.

    I though that the best way to accomplish this would be to grab the the last 4 characters of the strings in the wireNumber field on the wireInfo table and compare those 4 characters against the 4 characters in the checkNum field of the tlbWireNumCheck table.

    The checkNum field on the tlbWireNumCheck is currently being populated with numbers from 1 to 10000 by a function called AddNumbers()

    Code:
    Public Function AddNumbers()    Dim i As Long
        Dim rs As Recordset
        
            Set rs = CurrentDb.OpenRecordset("tblWireNumCheck", dbOpenDynaset)
            
                For i = 1 To 10000
                    rs.AddNew
                    rs!checkNum = i
                    rs.Update
                Next
                    rs.Close
                    Set rs = Nothing
    Then I have a form called rtpWireNumberGaps where a user will select the category of wire in which they are trying to find gaps. Once the user has selected the from the drop down menu, the user with click a button that will open a query labeled qryWireNumGap_TEST.

    This is the code for what happens when the Print Button on the rtpWireNumberGaps form is pressed:

    Code:
    Private Sub ptnWireNumGap_Click()Dim wireCatSelect As Integer
    Dim wireCatString As String
    
    
    wireCatSelect = Me.wireNumGapDropMenu
        If wireCatSelect = 1 Then
            wireCatString = "AES"
        ElseIf wireCatSelect = 2 Then
            wireCatString = "ASI"
        ElseIf wireCatSelect = 3 Then
            wireCatSring = "AUD"
        Else
        End If
        
    'I used the following line to see what I was getting
    'when a uses makes a selection from the dropdown menu
    'that is why I assigned wireCatSelect as an integer
    'and created the if statment to assign the string variable wireCatString.
    'MsgBox wireCatSelect, vbOKOnly, wireCatString
    
    
    'I am calling the AddNumbers Function because
    'the only way I know how to exicute a function is by calling the function
    
    
    Call AddNumbers
    
    
    DoCmd.OpenQuery qryWireNumGap_TEST, acViewNormal, acEdit
    
    
    
    
    
    
    End Sub
    This is the code for the SQL query that I have been trying:

    Code:
    SELECT checkNum
    FROM tblWireNumCheck
    WHERE checkNum Not In
    (Select CLng(Mid([wireNumber],4))
    From wireInfo
    Where Left([wireNumber],3) = From "rtpWireNumberGaps=" & wireCatString & "'" );
    Code:
    = From "rtpWireNumberGaps=" & wireCatString & "'" );
    is my attempt at passing the variable wireCatString to the qryWireNumGap_TEST

    So the process as I imagine it would go like this:

    A user navigates to the rptWireNumberGaps form and selects a category from the drop down menu and clicks print.
    Based on what catergory the user selects a string is assigned to a variable called wireCatString.
    Then that variable is passed on to the qryWireNumGap_TEST.

    The qryWireNumGap test pulls the last four characters from the strings that are in the wireNumber field on the wireInfo table and compares them against the strings that are in the checkNum field on the tblWireNumCheck table.

    Then the user would have a list containing all the gaps that are in that wire category.


    Now when I hard code what wire category I want to check, I get back a list of gaps that I would expect when I run open the query.

    For example

    Code:
    SELECT checkNum
    FROM tblWireNumCheck
    WHERE checkNum Not In
    (Select CLng(Mid([wireNumber],4))
    From wireInfo
    Where Left([wireNumber],3) = "AES")
    gives me a list of gaps that would be what I expect based on the wire numbers that are categorized as AES.

    Can I pass a variable to SQL query from a form?

    I would include a copy of my database, but I just accidently wiped it out so I will need to add all of this stuff over again and then post my database.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Seems number sequence is a big issue with your business. So bottom line is you are going to manage the number assignments and will not be using Access's autonumber?

    Can I pass a variable to SQL query from a form? YES, if using a query, the form must be open to use the control.

    Welcome back, by the way.

  3. #3
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by orange View Post
    Seems number sequence is a big issue with your business. So bottom line is you are going to manage the number assignments and will not be using Access's autonumber?

    Can I pass a variable to SQL query from a form? YES, if using a query, the form must be open to use the control.

    Welcome back, by the way.
    Its Good to be back.

    Yes, all of our cable runs revolve around the wire number as it is basically the only piece of information that we can guarantee is unique to each cable run.

    All of my tables have a primary key that is an auto number. Should I be comparing the auto primary key fields instead of the wireNumber and checkNum fields on the tables?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    isn't it a bit risky reusing numbers? today this number ran from X to Y location, tomorrow it runs from A to B? Wouldn't it be better to have a decommission date field?

    look to the bottom of this thread for others who have asked the same basic question

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Have a look at this: Note: original v1 replaced with v02 to correctly sort the split wirenumber field.

    drun-davegri-v02.zip

    The cable names are strings, which can present a sorting problem, so qWireSort separates the first three characters from the rest and sorts both fields.
    The listbox rowsource is from lookup table "Prefix". You can make multiple selects.
    The results are written to table "LogGap"

    Click image for larger version. 

Name:	loggaps.png 
Views:	58 
Size:	16.1 KB 
ID:	48308
    Last edited by davegri; 07-13-2022 at 09:29 AM. Reason: Replaced V01 with v02

  6. #6
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by CJ_London View Post
    isn't it a bit risky reusing numbers? today this number ran from X to Y location, tomorrow it runs from A to B? Wouldn't it be better to have a decommission date field?
    Yes, I feel like it is risky to reuse numbers, but my colleagues and I really don't reuse numbers that often. It is mostly my supervisor that likes to reuse wire numbers. The more that I have been thinking about this problem, the more I am wondering if doing something like a decommissioned field would be a better idea instead of deleting record from the database. I know that when I delete records it creates a gap in the primary key field, which I imagine might eventually cause a problem with the database in the future.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by drunyan0824 View Post
    I know that when I delete records it creates a gap in the primary key field, which I imagine might eventually cause a problem with the database in the future.
    Na. You've even got the option to make your autonumber field generate random numbers instead of incrementing sequentially.

    Moreover, let's say you start entering a field in a new record then cancel that record before inserting into the database. The next time you go to enter a record the autonumber will have skipped that last number.

    The autonumber field sequence is going to have holes in it... OCD be damned!

  8. #8
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    @davegri

    Okay, thank you for the help and I am sorry for not getting back to you sooner. I have been busy with finishing out family gatherings before the school year starts for my kids.

    The code that you provided worked like a charm, but in an effort to make sure that I am learning how to code in VBA and get access to do what I want, I'd like to go over the code if you don't mind.

    So first the SQL code on the query.

    Code:
    SELECT Left([WireNumber],3) AS WireType, CLng(Mid([WireNumber],4)) AS WireSeq
    FROM wireInfo
    ORDER BY Left([WireNumber],3), CLng(Mid([WireNumber],4));
    is saying grab the first 3 character from the string WireNumber and add that string to WireType then convert all of the characters from the 4 position of the string WireNumber to a long integer datatype and set that data to wireSeq. All of this data is being grabbed from the wireInfo Table. The third line in the SQL code simple sets how to display the information when the query is opened.


    Code:
    Private Sub cmdSearch_Click()
        Dim vari As Variant
        Call EmptyTable("LogGap")
        For Each vari In Me.lstPFX.ItemsSelected
            Call FindGap(Me.lstPFX.Column(1, vari))
        Next vari
    End Sub
    This code takes opens an empty table called LogGap and the prefix selection that the user selects and calls the FindGap Function.

    Code:
    Private Sub FindGap(PFX)
        Dim wi As DAO.Recordset, sSQL As String
        Dim seq As Long, bolFirst As Boolean: bolFirst = True
        Dim strip As Long, nSave As Long, nCount As Long, i As Integer
        Dim sGap As String
        Set wi = CurrentDb.OpenRecordset("Select * From qWireSort Where WireType ='" & PFX & "'")
        If wi.RecordCount = 0 Then Exit Sub
        
        With wi
            .MoveLast: .MoveFirst
            Do While Not .EOF Or .BOF
                strip = !wireSeq
                If bolFirst Then
                    nSave = strip
                    bolFirst = False
                    GoTo NextRec
                End If
                nCount = strip - nSave - 1
                For i = 1 To nCount
                    sGap = PFX & Format((nSave + i), "0000")  'Missing nums formatted from 0000 to 9999
                    sSQL = "Insert Into LogGap (Gap) Values('" & sGap & "')"
                    'Debug.Print sSQL
                    CurrentDb.Execute sSQL, dbFailOnError
                Next
    NextRec:
               nSave = strip
               .MoveNext
            Loop
        End With
    End Sub
    This code goes through the current record set that is created from qWireSort and incriments through each record and if there is a match between wireSeq and strip then nothing is written to the LogGap table, but if there is no match between strip and wireSeq then an entry is written to the LogGap table.

    Is that correct?

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    That pretty much covers it.
    The line "Call EmptyTable("LogGap") " actually deletes all records in table LogGap so that this run's result is uncontaminated by previous data.

    The cmcSearch_Click code deals with the multi-select listbox holding the prefixes. That code will call the FindGap procedure once for each prefix selected.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Another thing you have to consider, is that when you reuse deleted cable number, you must be sure all earlier links to this cable number in any other tables were deleted too (even if you user referential integrity to delete them along with cable info, in real life there is nothing you can be 100% sure about). So whenever you add a new cable number (NB! Any new cable number, except you add this cable number in same procedure which deletes old one, because later you can never be sure, was this number used before or not!), before adding the new number you have to check all linked tables for existence of same number, and delete them (or simply ran delete/update queries for each such table). Otherwise there will be a risk, that the new cable info is used where it doesn't belong! Is having all cable numbers without gaps worth the hassle?

  11. #11
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I can understand that you can't have gaps in invoice numbers, but what's the point in having sequential cable numbers that need to follow without gap? Logically I see no reason, only extra work and extra risks as Avril pointed out.

  12. #12
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    I can see the risks with re-using cable numbers and it is an issue that I will most likely have to address going forward. But a couple of reasons for finding using the gaps in the wire number is to try and eliminated people from just guessing what the next available wire number should be and also one of my co-workers will scan through the database to find gaps in the wire numbers. Also, a large portion of the gaps that occur in the database were never used in this database. Prior to using Access to keep track of the wire numbers, we were using an excel file. In that excel file, we would add wire numbers as we installed equipment and delete wire numbers as we decommissioned equipment so the excel file already had massive gaps in the wire numbers. That excel file served as the starting point for the access database that we've brought on-line. So currently, as things stand a large number of the gaps in the wire numbers have never be used in this database.

    But I will start checking the all of my tables the next time I delete a wire and see if everything that is associated with that wire number is deleted from the respective tables.

  13. #13
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    But a couple of reasons for finding using the gaps in the wire number is to try and eliminated people from just guessing what the next available wire number should be
    Why not create a function that will propose a new cable number according to your rules?

  14. #14
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    This is the first database that I've ever created so I am not really sure what I was doing. I know that I probably made some poor decisions in how I created my database. One of the forum admins recommended some tutorials and I need to make time to do those tutorials. I've been considering take an Access course that available though my local university's professional/continuing education programs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find missing numbers is a sequence qry
    By veale1009 in forum Queries
    Replies: 10
    Last Post: 02-04-2021, 03:43 PM
  2. Adding numbers in sequence
    By howlettb in forum Access
    Replies: 6
    Last Post: 12-31-2013, 02:30 PM
  3. Insert sequence of numbers in table
    By amrut in forum Queries
    Replies: 2
    Last Post: 05-09-2013, 07:00 AM
  4. Find First number in sequence of numbers
    By losstww in forum Queries
    Replies: 4
    Last Post: 11-16-2012, 02:44 PM
  5. Reverse Sequence of Numbers
    By OpsO in forum Queries
    Replies: 1
    Last Post: 08-12-2010, 06:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums