none
Min Max query doesn't account for missing sequence RRS feed

  • Question

  • We have a database that we use to track products that are returned for disposal. I am trying to automate the process of entering the data. I start by importing a spreadsheet that contains serial numbers into the database. After the import I run some queries that build a table containing the serial numbers with the model number of the product they are associated with and the sales order number that they originally went out on. Everything works great, even in testing, until one person brings up a scenario that we hadn't thought of. 

    Here's some background: A shipment went out 13 months ago for 10 bars. Serial numbers are A1 - A10. It is due to be returned for disposal. The return shipment comes in and there are only 8 bars in the package. The serial numbers returned are A1, A2, A3, A5, A6, A7, A8, and A9. My query uses the imported table to compare the serial numbers to the sales order detail table and adds the model number and the sales order number to each serial number record. I then run another query that uses the Min, max, and count features to determine which is the first serial number in the list and which is the last and how many there are total for that line. What it doesn't take into account is the gap. The missing serial number in the middle.

    Because it is grouping everything by model number and sales order number it doesn't care that there is one missing in the middle. So when the report prints it says 8 were returned A1 - A9. 

    Is there another way to do this or a way to make it see the gap?

    Thanks,

    Jessica

    Monday, March 4, 2019 7:38 PM

Answers

  • There are fairly standard SQL methods for returning subsequences or gaps in sequences.  All work by establishing the boundaries of the subsequence or gap.  Returning subsequnces, as you want to do, is not difficult.  The following query will return all subsequences separated by a gap of at least one from a set of sequential integer numbers (seat numbers in this example)

    SELECT Seat AS LowerVal,
        (SELECT MIN(Seat)
         FROM Seats AS S2
         WHERE S2.Seat > S1.Seat
         AND NOT EXISTS
             (SELECT Seat
              FROM Seats AS S3
              WHERE S3.Seat =S2.Seat+1)) AS UpperVal,
    UpperVal-LowerVal+1 AS SubSequenceLength
    FROM Seats AS S1
    WHERE NOT EXISTS
        (SELECT Seat
         FROM Seats AS S4
         WHERE S4.Seat = S1.Seat-1);

    In your case, as the serial numbers are alpha numeric you would need to parse the value to return the numeric part as an integer number.  If the numbers always begin with a single letter you'd merely substitute Val(Mid(Seat,2)) for Seat in the above query.

    Returning gaps in sequences is a little trickier as, unlike the above, the numbers to be returned do not exist in the set.  The query for this would be:

    SELECT LowerVal, MIN(UpperVals.UpperVal) AS UpperVal,
    UpperVal-LowerVal+1 AS GapLength
    FROM (SELECT Seat, Seat +1 AS LowerVal
                 FROM Seats AS S1
                 WHERE NOT EXISTS
                       (SELECT *
                       FROM Seats AS S2
                       WHERE S2.Seat = S1.Seat+1)
                       AND Seat <>
                              (SELECT MAX(Seat)
                               FROM Seats))  AS LowerVals INNER JOIN
                                   (SELECT Seat, Seat-1 AS UpperVal
                                    FROM Seats AS S3 WHERE NOT EXISTS
                                          (SELECT *
                                           FROM Seats AS S4
                                           WHERE S4.Seat = S3.Seat-1)
                                          AND Seat <>
                                               (SELECT MIN(Seat)
                                               FROM Seats))  AS UpperVals
                              ON LowerVals.Seat < UpperVals.Seat
    GROUP BY LowerVal;

    This query, because of its greater complexity, performs more slowly than the first query.

    Ken Sheridan, Stafford, England

    • Marked as answer by Jessicasdd Thursday, March 14, 2019 8:03 PM
    Tuesday, March 5, 2019 8:59 PM

All replies

  • Hi Jessica. If you're using code, you might be able to step from min to max and compare the result from the returned serials. This should tell you if there are any gaps. Just a thought...
    Monday, March 4, 2019 7:56 PM
  • I am using code for other parts of the process. My problem is I don't know how to write code very well. I'm still learning. I have better luck with queries because I can see what I am looking at, even though sometimes they don't work.

    So how might this be written in code? Can you provide an example? Even if it doesn't work for mine it's something to look at.

    Thanks,

    Jessica

    Monday, March 4, 2019 8:41 PM
  • Hi Jessica,

    Just a SWAG...

    For x = Min To Max
        If x NotExists In SerialList Then
            Add x to list of missing serials
        End If
    Next

    Actually, the above is just pseudo code because I have no idea what your list looks like. Hope it helps somewhat though...

    PS. On second thought, the Min and Max should be there because you're able to calculate those, so maybe you just need:

    For x = Min+1 To Max-1
    ...

    Good luck!

    Monday, March 4, 2019 9:47 PM
  • You can easily identify the missing items for a given sales order/model number with a query along these lines:

    SELECT OrderNumber, ModelNumber, SerialNumber
    FROM SalesOrders
    WHERE OrderNumber = [Enter order number:]
    AND ModelNumber = [Enter model Number:]
    AND NOT EXISTS
         (SELECT *
          FROM Returns
          WHERE Returns.OrderNumber = SalesOrders.OrderNumber
          AND Returns.ModelNumber = SalesOrders.ModelNumber
          AND Returns.SerialNumber = SalesOrders.SerialNumber);

    In a developed application the parameters would usually be references to controls in a dialogue form.


    Ken Sheridan, Stafford, England

    Monday, March 4, 2019 11:30 PM
  • Ok the second one won't work for me because the only information that is entered is the user's initials and the date the devices were returned. She doesn't know the order numbers or model numbers until after this has run. 

    The first one looks much simpler but I don't need to add the missing serial number back in. I need it to create two records. One with the first range and one with the second range. 

    So if I am using A1, A2, A3, A5, A6, A7, A8, and A9, I should have one record that has A1 - A3 and a second record with A5 - A9.

    So let me start from the beginning. I import the spreadsheet. It only contains serial numbers. I run a query that makes a table called RSWorkFile.

    SELECT ReturnedSerials.SerialNo, SerialNo_Lookup.MODEL, SerialNo_Lookup.[SO#], dbo_SorMaster.Customer INTO RSWorkFile
    FROM dbo_SorMaster INNER JOIN (ReturnedSerials INNER JOIN SerialNo_Lookup ON ReturnedSerials.SerialNo = SerialNo_Lookup.SN) ON dbo_SorMaster.SalesOrder = SerialNo_Lookup.[SO#]
    ORDER BY ReturnedSerials.SerialNo;

    It uses the tale that was created from the import, a table called SerialNo_Lookup (contains all serial numbers sold with their order numbers and product codes), and a table called SorMaster (from our ERP - contains order header information). 

    I then run a query to "massage" the data into groups to be added to the main Disposal table. 

    INSERT INTO RSMassaged ( MinOfSerialNo, MaxOfSerialNo, Qty, Model, [SO#], Customer, ReceivedDate, EnteredDate, [User] )
    SELECT Min(RSWorkFile.SerialNo) AS MinOfSerialNo, Max(RSWorkFile.SerialNo) AS MaxOfSerialNo, Count(RSWorkFile.SerialNo) AS CountOfSerialNo, RSWorkFile.MODEL, RSWorkFile.[SO#], RSWorkFile.Customer, [Forms]![frmRSWorkFile]![DateReceived] AS ReceivedDate, [Forms]![frmRSWorkFile]![DateEntered] AS EnteredDate, [Forms]![frmRSWorkFile]![Initials] AS [User]
    FROM RSWorkFile
    GROUP BY RSWorkFile.MODEL, RSWorkFile.[SO#], RSWorkFile.Customer, [Forms]![frmRSWorkFile]![DateReceived], [Forms]![frmRSWorkFile]![DateEntered], [Forms]![frmRSWorkFile]![Initials]
    ORDER BY Min(RSWorkFile.SerialNo);

    This is where it doesn't see the one that is missing. Because it is grouped by model, SO, and Customer it just looks at the full range and counts it. 

    Remember that these are queries. I need to add that code in either right before the last query or right after it. So with this information how can I make one of the codes (you've each provided) work?

    Thank you!!

    Jessica :)

    Tuesday, March 5, 2019 6:25 PM
  • There are fairly standard SQL methods for returning subsequences or gaps in sequences.  All work by establishing the boundaries of the subsequence or gap.  Returning subsequnces, as you want to do, is not difficult.  The following query will return all subsequences separated by a gap of at least one from a set of sequential integer numbers (seat numbers in this example)

    SELECT Seat AS LowerVal,
        (SELECT MIN(Seat)
         FROM Seats AS S2
         WHERE S2.Seat > S1.Seat
         AND NOT EXISTS
             (SELECT Seat
              FROM Seats AS S3
              WHERE S3.Seat =S2.Seat+1)) AS UpperVal,
    UpperVal-LowerVal+1 AS SubSequenceLength
    FROM Seats AS S1
    WHERE NOT EXISTS
        (SELECT Seat
         FROM Seats AS S4
         WHERE S4.Seat = S1.Seat-1);

    In your case, as the serial numbers are alpha numeric you would need to parse the value to return the numeric part as an integer number.  If the numbers always begin with a single letter you'd merely substitute Val(Mid(Seat,2)) for Seat in the above query.

    Returning gaps in sequences is a little trickier as, unlike the above, the numbers to be returned do not exist in the set.  The query for this would be:

    SELECT LowerVal, MIN(UpperVals.UpperVal) AS UpperVal,
    UpperVal-LowerVal+1 AS GapLength
    FROM (SELECT Seat, Seat +1 AS LowerVal
                 FROM Seats AS S1
                 WHERE NOT EXISTS
                       (SELECT *
                       FROM Seats AS S2
                       WHERE S2.Seat = S1.Seat+1)
                       AND Seat <>
                              (SELECT MAX(Seat)
                               FROM Seats))  AS LowerVals INNER JOIN
                                   (SELECT Seat, Seat-1 AS UpperVal
                                    FROM Seats AS S3 WHERE NOT EXISTS
                                          (SELECT *
                                           FROM Seats AS S4
                                           WHERE S4.Seat = S3.Seat-1)
                                          AND Seat <>
                                               (SELECT MIN(Seat)
                                               FROM Seats))  AS UpperVals
                              ON LowerVals.Seat < UpperVals.Seat
    GROUP BY LowerVal;

    This query, because of its greater complexity, performs more slowly than the first query.

    Ken Sheridan, Stafford, England

    • Marked as answer by Jessicasdd Thursday, March 14, 2019 8:03 PM
    Tuesday, March 5, 2019 8:59 PM
  • I can't figure this out. I'm not good enough at this to try to implement that kind of code.

    I've been trying to figure it out so that it makes sense with the information I have and I'm just not getting it. I really do appreciate you trying to help me though. And maybe it will help someone else, but I'm clueless here. 

    I am going to do some messing around to see if I can find another way without having to write code. 

    Thank you,

    Jessica

    Tuesday, March 12, 2019 7:43 PM
  • Is there a way to get a list to sort properly first because then I could just update the table with the other information. 

    So here is my list:

    <tfoot></tfoot>
    RSWorkFile
    SerialNo
    A2KG845A
    A2KG846A
    A2KG847A
    A2KG848A
    A2KG849A
    A2KG850A
    A2KG852A
    A2KG853A
    A2KG854A
    A2KG855A
    A2KG856A
    A2KG857A
    A2KG858A
    A2KG859A
    A2KG860A
    A2KG861A
    A2KG862A
    A2KG863A
    A2KG864A
    A2KG865A
    A2KG866A
    A2KG867A
    A2KG868A
    A2KG869A
    A2KG870A
    A2KG871A
    A2KG872A
    A2KG873A
    A2KG874A
    A2KL535A
    A2KL536A
    A2KL537A
    A2KL538A
    A2KM344
    A2KM373
    A2KM374
    A2KM375
    A2KM376
    A2KM377
    A2KM378
    A2KM379
    A2KM380
    A2KM381
    A2KM382
    A2KM383
    A2KM384
    A2KM385
    A2KM386
    A2KM387
    A2KM388
    A2KM389
    A2KM390
    A2KM393
    A2KM394
    A2KM395
    A2KM396
    A2KM397
    A2KM398
    A2KM399
    A2KM400
    A2KM401
    A2KM402
    A2KM431
    A2KM536
    A2KM558
    A2KM559

    If I put it together - first to last - it should look like this:

    <tfoot></tfoot>
    RSMassagedAAA
    MinOfSerialNo MaxOfSerialNo
    A2KG845A A2KG850A
    A2KG852A A2KG874A
    A2KL535A A2KL538A
    A2KM344
    A2KM373 A2KM375
    A2KM376 A2KM390
    A2KM393 A2KM402
    A2KM431
    A2KM536
    A2KM558 A2KM559

    If I can get that to happen first then I can make this work without the code. If there is no other way than to use code then I will have to bite the bullet and figure it out.

    Thanks,

    Jessica

    Tuesday, March 12, 2019 8:02 PM
  • Have you tried using the Find Unmatched Query Wizard? Click Create>>Query Wizard>>.

    If you already know which ones were shipped out, and you now have a query that knows which ones were returned, then the Find Unmatched Query Wizard can tell you which ones are missing.

    Tuesday, March 12, 2019 9:53 PM

  • RSMassagedAAA
    MinOfSerialNo MaxOfSerialNo
    A2KG845A A2KG850A
    A2KG852A A2KG874A
    A2KL535A A2KL538A
    A2KM344
    A2KM373 A2KM375
    A2KM376 A2KM390
    A2KM393 A2KM402
    A2KM431
    A2KM536
    A2KM558 A2KM559

    If I can get that to happen first then I can make this work without the code. If there is no other way than to use code then I will have to bite the bullet and figure it out.


    A small amendment to the query I posted earlier will return the boundaries of each continuous subsequence, including subsequences of length 1:

    SELECT Seat AS LowerVal,
        (SELECT MIN(Seat)
         FROM Seats AS S2
         WHERE S2.Seat >= S1.Seat
         AND NOT EXISTS
             (SELECT Seat
              FROM Seats AS S3
              WHERE S3.Seat =S2.Seat+1)) AS UpperVal,
    UpperVal-LowerVal+1 AS SubSequenceLength
    FROM Seats AS S1
    WHERE NOT EXISTS
        (SELECT Seat
         FROM Seats AS S4
         WHERE S4.Seat = S1.Seat-1);

    In your case as the values are alphanumeric rather than integers you will need to parse the values so that each distinct value of the first four characters is given a number starting at 1000 and incrementing by 1000.  By adding this to the following numeric value this will give you distinct numbers 1845, 1846…….2535, 2536 and so on.  A query like the above can then be used to return the start, end, and length of each subsequence.  Numbering groups in increments of 1000 is done with a query like the following example, which does this for each group of rows per LastName value in a Contacts table:

    SELECT LastName,
       (SELECT COUNT(*)
        FROM (SELECT DISTINCT LastName
                     FROM Contacts) AS T2
         WHERE T2.LastName <= T1.LastName) * 1000 AS GroupNumber
    FROM Contacts AS T1
    GROUP BY LastName;


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, March 12, 2019 10:51 PM Punctuated.
    Tuesday, March 12, 2019 10:49 PM
  • Hi Lawrence - thank you for the suggestion but I'm not looking to fill in the missing serial number. I want it to skip over it and correctly count what is remaining, as well as, recognizing the gap. So the records would be 1-6 (count = 6) and 8-9 (count = 2), for a total of 8, instead of 1-9 (count = 9). 

    Hi Ken,

    If I break the serial numbers down so that there is a prefix (A2KG) and a suffix (845A), would it be easier to code? I know that the values being alphanumeric are causing a problem. The first 4 characters will always remain the same. So will the last 3 or 4, but those last 3 or 4 are the sequential numbers. I can use a query to split them into 2 columns and then maybe apply code to group them properly or easier. I can put them back together in another query prior to adding the rest of the information to the table (SO#, Model, Customer).

    Thanks,

    Jessica

    Wednesday, March 13, 2019 5:23 PM
  • All of my data is text. This is going to be another issue. :(

    I was just trying to see if I could add 1 to the suffix and it's giving me a data type mismatch. i went back into the imported table and everything is text. And I think because it is alphanumeric it can't be changed. Or can it to do what I need and be changed back? I think that's too much.

    I'm thinking I might have to have a manual step in the middle where maybe she adds a grouping number to the table, just to make this easier. 

    Or can this be done in the excel file prior to importing it?

    Thanks,

    Jessica

    Wednesday, March 13, 2019 5:51 PM
  • I'm thinking I might have to have a manual step in the middle where maybe she adds a grouping number to the table, just to make this easier.
    That would make it easier, but the group numbers must be in increments of 1000, i.e. 1000, 2000, 3000 and so on.  You can then add the group number to the 3 digit number, which can be obtained with:

        Val(Mid(SerialNo,5))

    to give you a set of distinct integer numbers which the first query in my last replay can process.  It's not difficult to compute the group numbers, however, as shown in my second query.  In your case you'd use Left(Serial,No,4) to return the values on which to compute the group numbers.  Computing the numbers rules out the risk of error in entering them manually.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, March 13, 2019 6:10 PM Typo corrected.
    Wednesday, March 13, 2019 6:09 PM
  • I was thinking in the next column. More like this...

    SerialNo Group
    A2KG845A     1
    A2KG846A     1
    A2KG847A     1
    A2KG848A     1
    A2KG849A     1
    A2KG850A     1
    A2KG852A     2
    A2KG853A     2
    A2KG854A     2
    A2KG855A     2
    A2KG856A     2
    A2KG857A     2
    A2KG858A     2
    A2KG859A     2
    A2KG860A     2
    A2KG861A     2
    A2KG862A     2

    Then I could group by that number and my min and max would be based on that grouping. But I think that brings me back to trying to figure out code.

    I'm so ready to just say nope this won't work and let them continue to manually enter everything. 

    If I don't understand it I'm getting nowhere and I feel like I am wasting your time. And I'm sorry because I really don't want to waste anyone's time. 

    Thanks,

    Jessica

    Wednesday, March 13, 2019 7:41 PM
  • If you do that, then returning the start, end and length of each group becomes a matter of a very simple aggregating query, e.g.

    SELECT MIN(SerialNo) AS LowerVal,
                 MAX(SerialNo) AS UpperVal,
                 COUNT(*) AS GroupLength
    FROM TableNameGoesHere
    GROUP BY [Group];

    Do not use Group as a column name however.  As you can see its an SQL keyword, so should not be used as an object name.  Use ReturnGroup or similar instead.

    This would of course be very labour intensive with a long list, and, as I pointed out earlier, risks errors in input.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, March 13, 2019 11:05 PM Typo corrected.
    Wednesday, March 13, 2019 11:03 PM
  • Yeah that was how I had originally tried it but without the Group column so I was getting the full range of serials with a count of 29 even though the range added up to 30. It was skipping over the missing one and not counting it but not accounting for it in the range. So that didn't work. 

    But I have to find a way to make this work without her having to do anything otherwise she won't use it. She said if she has to do any manual input then she might as well continue to type everything manually also. 

    So I am trying to make sense of this:

    SELECT Seat AS LowerVal,
        (SELECT MIN(Seat)
         FROM Seats AS S2
         WHERE S2.Seat >= S1.Seat
         AND NOT EXISTS
             (SELECT Seat
              FROM Seats AS S3
              WHERE S3.Seat =S2.Seat+1)) AS UpperVal,
    UpperVal-LowerVal+1 AS SubSequenceLength
    FROM Seats AS S1
    WHERE NOT EXISTS
        (SELECT Seat
         FROM Seats AS S4
         WHERE S4.Seat = S1.Seat-1);

    Or this:

    SELECT LastName, 
       (SELECT COUNT(*)
        FROM (SELECT DISTINCT LastName
                     FROM Contacts) AS T2
         WHERE T2.LastName <= T1.LastName) * 1000 AS GroupNumber
    FROM Contacts AS T1
    GROUP BY LastName;

    I am using the table that split the serials into prefix and suffix. Will this matter that all of the data is text?

    My table name is Split_Serials. The columns are Prefix and Suffix. So is this right?

    SELECT Suffix, 
       (SELECT COUNT(*)
        FROM (SELECT DISTINCT Suffix
                     FROM Split_Serials) AS T2
         WHERE T2.Suffix <= T1.Suffix) * 1000 AS GroupNumber
    FROM Split_Serials AS T1
    GROUP BY Suffix;

    Thanks,

    Jessica

    Thursday, March 14, 2019 3:25 PM
  • I don't think that's what i was supposed to get...

    <tfoot></tfoot>
    Query1
    Expr1 GroupNumber
    344 1000
    373 2000
    374 3000
    375 4000
    376 5000
    377 6000
    378 7000
    379 8000
    380 9000
    381 10000
    382 11000
    383 12000
    384 13000
    385 14000
    386 15000
    387 16000
    388 17000
    389 18000
    390 19000
    393 20000
    394 21000
    395 22000
    396 23000
    397 24000
    398 25000
    399 26000
    400 27000
    401 28000
    402 29000
    431 30000
    535A 31000
    536 32000
    536A 33000
    537A 34000
    538A 35000
    558 36000
    559 37000
    845A 38000
    846A 39000
    847A 40000
    848A 41000
    849A 42000
    850A 43000
    852A 44000
    853A 45000
    854A 46000
    855A 47000
    856A 48000
    857A 49000
    858A 50000
    859A 51000
    860A 52000
    861A 53000
    862A 54000
    863A 55000
    864A 56000
    865A 57000
    866A 58000
    867A 59000
    868A 60000
    869A 61000
    870A 62000
    871A 63000
    872A 64000
    873A 65000
    874A 66000

    Thursday, March 14, 2019 3:28 PM
  • SELECT SNSuffix AS LowerVal,
        (SELECT MIN(SNSuffix)
         FROM Serials_Split AS S2
         WHERE S2.SNSuffix >= S1.SNSuffix
         AND NOT EXISTS
             (SELECT SNSuffix
              FROM Serials_Split AS S3
              WHERE S3.SNSuffix =S2.SNSuffix+1)) AS UpperVal,
    UpperVal-LowerVal+1 AS SubSequenceLength
    FROM Serials_Split AS S1
    WHERE NOT EXISTS
        (SELECT SNSuffix
         FROM Serials_Split AS S4
         WHERE S4.SNSuffix = S1.SNSuffix-1);

    Gave me a data mismatch error. I figure that's because the data is all text and not integers. 

    Thanks,

    Jessica

    Thursday, March 14, 2019 3:36 PM
  • I don't think that's what i was supposed to get...

    <tfoot></tfoot>
    Query1
    Expr1     GroupNumber
    344        1000
    373        2000
    374        3000
    ........
    872A      64000
    873A      65000
    874A      66000

    Instead of the 3-digit number plus the letter suffix, if any, you should be returning the distinct first four characters of the serial number in the query.  You can then join this to the original table on that substring in a further query, in which the group number should be added to the value of the 3-digit number following the first four characters.  I gave you the expression for this earlier.  This will give you a column of distinct integer numbers such as 1845 corresponding to A2KG845A, 1846 corresponding to A2KG846A and so on, 2535 corresponding to A2KL535, 2536 corresponding to A2KL536, and so on.  My first query can then operate on those integer numbers to return the start, end and length of each continuous subsequence.


    Ken Sheridan, Stafford, England

    Thursday, March 14, 2019 5:29 PM
  • It works!!! I got it to work!! OMG I'm so excited right now. You are the MAN!!!

    Ok so what I did was after importing the Excel spreadsheet I broke the serial number down into 3 parts. SNPrefix = A2AA, SNSuffix = 123, and SNA = the A on the end of the serial nunber. That A isn't always there. Anyway, for the SNSuffix I did SNSuffix: Val(Mid([SerialNo],5,3)) because the serial number is always the same format (unless it's really old - which I'm not concerned about) and we needed the number part of it to NOT be text.

    Then I ran this:

    SELECT SNSuffix AS LowerVal,
        (SELECT MIN(SNSuffix)
         FROM Serials_Split AS S2
         WHERE S2.SNSuffix >= S1.SNSuffix
         AND NOT EXISTS
             (SELECT SNSuffix
              FROM Serials_Split AS S3
              WHERE S3.SNSuffix =S2.SNSuffix+1)) AS UpperVal,
    UpperVal-LowerVal+1 AS SubSequenceLength
    FROM Serials_Split AS S1
    WHERE NOT EXISTS
        (SELECT SNSuffix
         FROM Serials_Split AS S4
         WHERE S4.SNSuffix = S1.SNSuffix-1);

    And it gave me:

    <tfoot></tfoot>
    qWorkFileTestSQL
    LowerVal UpperVal SubSequenceLength
    845 850 6
    852 874 23
    535 538 4
    344 344 1
    373 390 18
    393 402 10
    431 431 1
    558 559 2

    So now I should be able to put them back together and finish the steps to put them in their proper places.

    Of course I will update when I finish all of that because I want to make sure the rest still works. I'll work on it more tomorrow. 

    But in the mean time - THANK YOU THANK YOU THANK YOU!!!

    Jessica :)

    Thursday, March 14, 2019 8:01 PM
  • So I had a little hiccup with this. I put everything back together to run the process and found that it had combined a range of serial numbers that were for two different model numbers. So I went in and tweaked it to also look at the MODEL when comparing the tables. 

    Here is the final code (so far...) 

    SELECT S1.SNSuffix AS LowerVal, (SELECT MIN(SNSuffix)

    FROM Serials_Split AS S2

    WHERE S2.SNSuffix >= S1.SNSuffix

    AND NOT EXISTS

    (SELECT SNSuffix

    FROM Serials_Split AS S3

    WHERE S1.MODEL = S3.MODEL AND S3.SNSuffix =S2.SNSuffix+1)) AS UpperVal, UpperVal-LowerVal+1 AS SubSequenceLength, S1.MODEL, S1.[SO#], S1.Customer, S1.ReceivedDate, S1.EnteredDate, S1.User INTO RSMassagedA

    FROM Serials_Split AS S1

    WHERE (((Exists (SELECT SNSuffix

    FROM Serials_Split AS S4

    WHERE S1.MODEL = S4.MODEL AND S4.SNSuffix = S1.SNSuffix-1))=False));


    Thanks,

    Jessica :)

    Wednesday, March 20, 2019 3:32 PM
  • So we were finally able to set up the computer so they could use this and low and behold it didn't work. Grrrrr!!!

    If the serial number is something like A2AA001A, when the query splits it into its three parts A2AA - 001 - A it drops any leading zeroes. So the serial number came in as A2AA1A. I was able to fix this by deleting the info in the tables and appending to them instead of making them every time. This way I was able to format the fields to have the leading zeroes.

    And for some reason I have one serial number that is being combined with another grouping from a completely different company. I can't find where to fix it.

    Here is the SQL query #3 that shows the companies properly:

    INSERT INTO Serials_Split ( SNPrefix, SNSuffix, SNA, MODEL, [SO#], Customer, ReceivedDate, EnteredDate, [User] )
    SELECT Left([SerialNo],4) AS SNPrefix, Val(Mid([SerialNo],5,3)) AS SNSuffix, Mid([SerialNo],8) AS SNA, RSWorkFile.MODEL, RSWorkFile.[SO#], RSWorkFile.Customer, [Forms]![frmRSWorkFile]![DateReceived] AS ReceivedDate, [Forms]![frmRSWorkFile]![DateEntered] AS EnteredDate, [Forms]![frmRSWorkFile]![Initials] AS [User]
    FROM RSWorkFile;

    Here is the SQL query where it is combining serials from two different companies:

    INSERT INTO RSMassagedA ( LowerVal, UpperVal, SubSequenceLength, MODEL, [SO#], Customer, ReceivedDate, EnteredDate, [User] )
    SELECT S1.SNSuffix AS LowerVal, (SELECT MIN(SNSuffix)
    FROM Serials_Split AS S2
    WHERE S2.SNSuffix >= S1.SNSuffix
    AND NOT EXISTS
    (SELECT SNSuffix
    FROM Serials_Split AS S3
    WHERE S1.MODEL = S3.MODEL AND S3.SNSuffix =S2.SNSuffix+1)) AS UpperVal, UpperVal-LowerVal+1 AS SubSequenceLength, S1.MODEL, S1.[SO#], S1.Customer, S1.ReceivedDate, S1.EnteredDate, S1.User
    FROM Serials_Split AS S1
    WHERE (((Exists (SELECT SNSuffix
    FROM Serials_Split AS S4
    WHERE S1.MODEL = S4.MODEL AND S4.SNSuffix = S1.SNSuffix-1))=False));

    I think I have to add a S1.Customer = S?.Customer or maybe a S1[SO#] = S?.[SO#] somewhere to fix it but I'm drawing a blank.

    Thanks,

    Jessica


    • Edited by Jessicasdd Tuesday, July 23, 2019 6:56 PM Missing information
    Tuesday, July 23, 2019 6:30 PM
  • I was wrong. I don't have the missing zeroes figured out. It works up until I put the serial numbers back together. It drops the leading zero before combining the fields. I don't know how to fix this. 

    Thanks,

    Jessica

    Tuesday, July 23, 2019 7:35 PM
  • Use the Format function to return an integer number as a string expression with leading zeros.  You can see this by entering some literal values in the immediate window:

    ? Format(1,"000")
    001
    ? Format(42,"000")
    042
    ? Format(666,"000")
    666

    Ken Sheridan, Stafford, England

    Tuesday, July 23, 2019 8:18 PM
  • That worked. I kept trying different ways of writing it and just couldn't get it. I kept getting Invalid Syntax. 

    Now to figure out the Customer. 

    I have one line that is being grouped into a range without taking into account that it's a different customer. Step 3 lists all customers properly but step 4 combines the ranges and eliminates the one customer. I can't figure out why.

    Thanks,

    Jessica

    Wednesday, July 24, 2019 1:20 PM
  • To identify the subsequences per customer the subquery would need to be correlated with the outer query on Customer, which I think would mean adding:

        AND S1.Customer = S4.Customer

    to the subquery's WHERE clause.  The following is an example from my Subsequences demo at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    SELECT S1.Code, S1.SeqNumber AS SubsequenceStart,
    MIN(S2.SeqNumber) AS SubsequenceEnd,
    (SubsequenceEnd-SubsequenceStart)+1 AS Length
    FROM Sequences AS S1, Sequences AS S2
    WHERE S1.SeqNumber <= S2.SeqNumber
    AND S1.Code = S2.Code
       AND NOT EXISTS
          (SELECT *
           FROM Sequences AS S3
           WHERE S3.Code = S1.Code
           AND S3.SeqNumber NOT BETWEEN S1.SeqNumber AND S2.SeqNumber
           AND (S3.SeqNumber = S1.SeqNumber-1 OR S3.SeqNumber = S2.SeqNumber+1))
    GROUP BY S1.Code, S1.SeqNumber;

    In this case the correlation is on Code.

    Ken Sheridan, Stafford, England

    Wednesday, July 24, 2019 4:32 PM
  • Hi Ken,

    I tried adding that in a couple different places yesterday and couldn't get it to work. I just added it now and it worked. Who knows. LOL Just glad it worked. I still have the one serial number that goes with that separate company being sequenced with the entry before it. 

    Here is the code (the old is where I added the customer):

    INSERT INTO RSMassagedA ( LowerVal, UpperVal, SubSequenceLength, MODEL, [SO#], Customer, ReceivedDate, EnteredDate, [User] )
    SELECT S1.SNSuffix AS LowerVal, (SELECT MIN(SNSuffix)
    FROM Serials_Split AS S2
    WHERE S2.SNSuffix >= S1.SNSuffix
    AND NOT EXISTS
    (SELECT SNSuffix
    FROM Serials_Split AS S3
    WHERE S1.MODEL = S3.MODEL AND S3.SNSuffix =S2.SNSuffix+1)) AS UpperVal, UpperVal-LowerVal+1 AS SubSequenceLength, S1.MODEL, S1.[SO#], S1.Customer, S1.ReceivedDate, S1.EnteredDate, S1.User
    FROM Serials_Split AS S1
    WHERE (((Exists (SELECT SNSuffix
    FROM Serials_Split AS S4
    WHERE S1.MODEL = S4.MODEL AND S1.Customer = S4.Customer AND S4.SNSuffix = S1.SNSuffix-1))=False));

    So now should I group somewhere on the Customer?

    Here is the data:

    <tfoot></tfoot>
    qRSWorkFile_step04
    LowerVal UpperVal SubSequenceLength MODEL SO# Customer ReceivedDate EnteredDate User
    252 253 2 P-2042 000000000066563 FRE4730


    254 255 2 P-2001 000000000066563 FRE4730


    256 273 18 P-2042 000000000066566 FRE4730


    274 282 9 P-2001 000000000066568 FRE4730


    321 321 1 P-2001 000000000066602 MSC0030


    323 330 8 P-2001 000000000066602 MSC0030


    054 56 3 P-2021 000000000067258 LOC5500


    057 58 2 P-2031 000000000067258 LOC5500


    463 463 1 P-2021 000000000067352 UNI1024


    515 517 3 P-2021 000000000067403 HON6100


    534 539 6 P-2021 000000000067355 SPE0090


    539 539 1 P-2021 000000000067375 DET0060


    429 429 1 P-2042 000000000067722 LOC1801


    It corrects the missing leading zero in the next step, but you can see that 539 is in 3 places. It should only be on one line.

    Thanks,

    Jessica


    • Edited by Jessicasdd Wednesday, July 24, 2019 6:20 PM Added more info
    Wednesday, July 24, 2019 6:16 PM
  • I don't understand why your SQL statement uses two subqueries rather than one as in my examples.  It seems unnecessarily complex.  All I can suggest is that you try correlating both subqueries with the outer query on Customer.

    Ken Sheridan, Stafford, England

    Wednesday, July 24, 2019 9:42 PM
  • I honestly don't understand the SQL statement so I really can't explain why it is what it is. All I did was modify what you provided back on March 12th. 

    I thought maybe if I just turned totals on in the design view (because it's in Access) that maybe it would group properly but it gave a Syntax error. 

    I tried adding a GROUP BY S1.Customer at the end of the SQL statement but I get a pop-up that says, " You tried to execute a query that does not include the specified expression 'LowerVal' as part of an aggregate function." - I have no clue what that means.

    I tried adding  AND S1.[SO#] = S4.[SO#] but that didn't help wither.

    Thanks,

    Jessica

    Thursday, July 25, 2019 12:30 PM
  • The original query I posted does use two subqueries.  I think this method was originally published by Joe Celko.  The methods in my Subsequences demo are simpler and use only one, however, which is what confused me with regard to your query.

    I would suspect that the reason for the duplication of certain values is that those values are in fact present in subsequences for more than one grouping in the base data.  The last query I posted does, as noted in my demo, allow for this, although the example in the demo does use distinct values.  I'm not sure whether the Celko method would allow for duplicates.  From memory I think his published examples do not return subsequences per group, only for the complete set, so it's hard to say.

    If the duplicated values are not in fact present as base data in more than one group, then you might like to try my simpler method.  In my demo this does return distinct subsequences, as you can see in the following image:


    Ken Sheridan, Stafford, England

    Thursday, July 25, 2019 4:18 PM
  • <tfoot></tfoot>
    qRSWorkFile_Step03
    SNPrefix SNSuffix SNA MODEL SO# Customer ReceivedDate EnteredDate User
    A2LG 252
    P-2042 000000000066563 FRE4730


    A2LG 253
    P-2042 000000000066563 FRE4730


    A2LG 254
    P-2001 000000000066563 FRE4730


    A2LG 255
    P-2001 000000000066563 FRE4730


    A2LG 256
    P-2042 000000000066566 FRE4730


    A2LG 257
    P-2042 000000000066566 FRE4730


    A2LG 258
    P-2042 000000000066566 FRE4730


    A2LG 259
    P-2042 000000000066566 FRE4730


    A2LG 260
    P-2042 000000000066566 FRE4730


    A2LG 261
    P-2042 000000000066566 FRE4730


    A2LG 262
    P-2042 000000000066566 FRE4730


    A2LG 263
    P-2042 000000000066566 FRE4730


    A2LG 264
    P-2042 000000000066566 FRE4730


    A2LG 265
    P-2042 000000000066566 FRE4730


    A2LG 266
    P-2042 000000000066566 FRE4730


    A2LG 267
    P-2042 000000000066566 FRE4730


    A2LG 268
    P-2042 000000000066566 FRE4730


    A2LG 269
    P-2042 000000000066566 FRE4730


    A2LG 270
    P-2042 000000000066566 FRE4730


    A2LG 271
    P-2042 000000000066566 FRE4730


    A2LG 272
    P-2042 000000000066566 FRE4730


    A2LG 273
    P-2042 000000000066566 FRE4730


    A2LG 274
    P-2001 000000000066568 FRE4730


    A2LG 275
    P-2001 000000000066568 FRE4730


    A2LG 276
    P-2001 000000000066568 FRE4730


    A2LG 277
    P-2001 000000000066568 FRE4730


    A2LG 278
    P-2001 000000000066568 FRE4730


    A2LG 279
    P-2001 000000000066568 FRE4730


    A2LG 280
    P-2001 000000000066568 FRE4730


    A2LG 281
    P-2001 000000000066568 FRE4730


    A2LG 282
    P-2001 000000000066568 FRE4730


    A2LG 321
    P-2001 000000000066602 MSC0030


    A2LG 323
    P-2001 000000000066602 MSC0030


    A2LG 324
    P-2001 000000000066602 MSC0030


    A2LG 325
    P-2001 000000000066602 MSC0030


    A2LG 326
    P-2001 000000000066602 MSC0030


    A2LG 327
    P-2001 000000000066602 MSC0030


    A2LG 328
    P-2001 000000000066602 MSC0030


    A2LG 329
    P-2001 000000000066602 MSC0030


    A2LG 329
    P-2001 000000000066602 MSC0030


    A2LG 330
    P-2001 000000000066602 MSC0030


    A2LJ 54
    P-2021 000000000067258 LOC5500


    A2LJ 55
    P-2021 000000000067258 LOC5500


    A2LJ 56
    P-2021 000000000067258 LOC5500


    A2LJ 57
    P-2031 000000000067258 LOC5500


    A2LJ 58
    P-2031 000000000067258 LOC5500


    A2LJ 463
    P-2021 000000000067352 UNI1024


    A2LJ 515
    P-2021 000000000067403 HON6100


    A2LJ 516
    P-2021 000000000067403 HON6100


    A2LJ 517
    P-2021 000000000067403 HON6100


    A2LJ 534
    P-2021 000000000067355 SPE0090


    A2LJ 535
    P-2021 000000000067355 SPE0090


    A2LJ 536
    P-2021 000000000067355 SPE0090


    A2LJ 537
    P-2021 000000000067355 SPE0090


    A2LJ 538
    P-2021 000000000067355 SPE0090


    A2LJ 539
    P-2021 000000000067375 DET0060


    A2LK 429
    P-2042 000000000067722 LOC1801


    Thursday, July 25, 2019 5:21 PM
  • That's the data in the table that the query is using. The line that gets combined is second from last. It gets pushed into the information above it. It has a different SO# and Customer. So I think if I can just find a place to add that those have to equal each other then maybe it will work. 

    I don't want to have to start over. But I will if I absolutely have to.

    Thanks,

    Jessica

    Thursday, July 25, 2019 5:25 PM
  • This worked - and again I HAD to put it in two places (where it's BOLD) or it didn't work...

    INSERT INTO RSMassagedA ( LowerVal, UpperVal, SubSequenceLength, MODEL, [SO#], Customer, ReceivedDate, EnteredDate, [User] )
    SELECT S1.SNSuffix AS LowerVal, (SELECT MIN(SNSuffix)
    FROM Serials_Split AS S2
    WHERE S2.SNSuffix >= S1.SNSuffix
    AND NOT EXISTS
    (SELECT SNSuffix
    FROM Serials_Split AS S3
    WHERE S1.MODEL = S3.MODEL AND S1.Customer = S3.Customer AND S3.SNSuffix =S2.SNSuffix+1)) AS UpperVal, UpperVal-LowerVal+1 AS SubSequenceLength, S1.MODEL, S1.[SO#], S1.Customer, S1.ReceivedDate, S1.EnteredDate, S1.User
    FROM Serials_Split AS S1
    WHERE (((Exists (SELECT SNSuffix
    FROM Serials_Split AS S4
    WHERE S1.MODEL = S4.MODEL AND S1.Customer = S4.Customer AND S4.SNSuffix = S1.SNSuffix-1))=False));

    If I only had it in the bottom it didn't work and if it was only in the top one it didn't work. As soon as i put it in both it worked. I'm not sure why but I'm trying to make sense of it because I really want to understand it. I've taken some classes but the inner outer joins are confusing to me and all the parenthesis confuse me. I think that S1, S2, S3, and S4 are the table Serials_Split being joined to itself. I know that anywhere it says WHERE S? = S? are the actual joins. Those would be the same if looking at two table in access and joining customer from one table to customer in the other table. The other code, I think, is telling it how to determine the LowerVal (first serial number in the range) and UpperVal (last number in the range) and SubSequenceLength (Quantity). 

    Ok I'm going to run a full test and see what happens. I'll be back! :)

    Thanks,

    Jessica

    Thursday, July 25, 2019 5:46 PM
  • It worked. Everything lined up properly and went into their proper buckets and the information was correct for each record. 

    Thank you,

    Jessica :)

    Friday, July 26, 2019 12:00 PM