Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: looking for missing numbers in a sequence

    One of my fields has data that looks like this (student ID numbers):

    A08-0001
    A08-0002
    A08-0004
    B05-0001
    B06-0001
    B06-0002
    B06-0005
    B07-0001

    The first letter is a code, the next two digits is the year ('05 - '08) and the last four is the sequence. The letter codes in use are letters A to L, more letters may be added according to need and the years start from 2005 and will added to... uhm, annually. Each code+year combination starts at 0001. I want to look for gaps in the numeric sequences per combination. These missing ID numbers are unused.

    In the example above, A08-0003, B06-0003 & B06-0004 are missing, I want to make a form that automatically finds and lists down all these numbers so they can be used (it would be a shame to waste a number!). =D

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coffeecat
    it would be a shame to waste a number!
    if you don't mind an opposing opinion, it would be silly to find the ones that are missing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    @rudy:
    silly is a non-developer with no coding skills who is making a database from ground-up (that would be me, and hence the whole dilemma), so uh, yeah, i do mind! hehe =p

    On a side note, the numbers can serve as an unofficial count of students per program each year (short of a manual dcount or something). I'm also saving myself the hassle of explaining why the last G08 number ends with "-0204" when only 197 students are enrolled in the G program for '08.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just tell 'em if they want the numbers in a school year run the stats form, where you use dcount as often as you need
    or better yet do it as single query, somehting similar to

    tell 'em its none of there somethign business the student number doesn't have anythign to do with the nuber fo students its merely an identifier.. dos your bank accoutn identify the nuber fo accounts that bank has?, dos you National Insurance/SS/tax number reflect the nuber fo taxpayers.

    if you are concerned about such questions then play the cute game.. add a check digit to the end of the number so that you can enforce data integrity.

    that way round when they type in the number you have additional comfort of knowing its not a typo..... its your system.. not "theirs", at least no till they signed off on it.

    select count(howeveryoudecodeyouryear) as nopupils frm my table group by howeveryoudecodeyouryear

    in my books pupils are at school (or in the eyes) whereas students are at university or college.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, I'd be restructuring that "code" if I were you.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jun 2008
    Posts
    163
    You guys raise a good point. I know its being stubborn of me, but I myself still like knowing how to find missing numbers in sequences like these. In a way, this is how I'm learning vba/sql and access in general. While a solution to this question is hardly practical, the knowledge may still come in handy.

    Thanks for the help, I'll go see if I can make it work. =D

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, fine, i'll walk you through it

    let's start off with a couple of questions

    presumably the number portion of the code cannot go higher than 9999?

    if the highest number is X08-0937, do you want to see that 0938 through 9999 are missing?

    also a bit of preparatory work, please create a table called integers like this (note this is standard sql, so it won't work in access, but you should be able to build yours from this) --
    Code:
    CREATE TABLE integers (i INTEGER NOT NULL PRIMARY KEY);
    INSERT INTO integers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
    now create a query like this:
    Code:
    SELECT 1000*m.i+100*c.i+10*x.i+u.i
      FROM integers m, integers c, integers x, integers u
    save this query and call it q10k

    let me know when you're ready
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2008
    Posts
    163
    Thanks, I appreciate it.

    Yep. the numbers should not go higher than 9999 (business-wise, I'd be happy if they did, and I'll happily create a new code for the year that will start all-over again from 0001).

    Numbers are missing if they are gaps between 0001 and the last used number, so I guess that there should be a check of sorts to see what the last number used was. Its actually a little easier since I have a table which keeps track of the last number used.

    I have an enrollment form that has a drop down box containing three-character codes like A08, B08, C08, etc. The user will select the appropriate code, and when finished with the rest of the data, a button will generate the ID number by checking the three-digit code in another table. That table is a list of the three digit codes and the last number used for the sequence. they all start with zero and given a +1 for every new ID.

    The gaps come from deleting used ID numbers (like the wrong code applied in the enrollment form, it can be deleted and a new Id number can be generated). I do not use a -1 for deletes because it will cause errors when deleting ID codes that are old. As a quick last resort, a user may choose to manually input an ID number (it does not accept duplicates), but that method is least encouraged to my users (fortunately, I work with a very small group and everyone is quite cooperative when it comes to following directions).

    Anyway, I created a table called integers, 4 number fields (m, c, x, and i), ten records each, with values 0 to 9. No PK. I also made the query (q10k). Running the query gave me a single column result, 10000 records, numbers 0-9999.

    I hope I did it right. Let me know if there is anything I should change.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The integers table should have one field called i that is an integer and primary key.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, one glitch, i forgot to name the column in the q10k query
    Code:
    SELECT 1000*m.i+100*c.i+10*x.i+u.i AS i10k
      FROM integers m, integers c, integers x, integers u
    create another query called codeyears --
    Code:
    SELECT LEFT(studID,4) AS codeyear
         , MAX(studID) AS lastused
      FROM students
    GROUP
        BY LEFT(studID,4)
    now we can find the missing numbers
    Code:
    SELECT x.studID
      FROM ( SELECT codeyear & RIGHT('0000'&i10k,4) AS studID
               FROM codeyears, q10k 
              WHERE studID <= lastused ) AS x
    LEFT OUTER
      JOIN students
        ON students.studID = x.studID
     WHERE students.studID IS NULL
    caution, not tested, and i'll bet Access barfs on something here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2008
    Posts
    163
    I fixed the PK and integer properties settings on the Integers table, and also added "AS i10k" to name the field in the q10k query (I'm picking up a lot of useful commands, thanks guys).

    Query codeyears works fine, it outputs two columns, one lists the three letter codes being used and the second column lists the last number in the sequence. [list - students] is the table name and SID is the field name.

    The second one gave me an error:
    "microsoft jet engine database cannot find the input table or query 'SELECT codeyear & RIGHT ('0000'&i10k,4) AS SID FROM codeyears, q10k WHERE SID <= lastused'. Make sure it exists and that its name is spelled correctly."

    my sql is:
    SELECT x.SID
    FROM [SELECT codeyear & RIGHT('0000'&i10k,4) AS SID FROM codeyears, q10k WHERE SID <= lastused ] AS x LEFT JOIN [list - students] ON [list - students].SID=x.SID
    WHERE [list- students].SID IS NULL;

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    stupid, stupid ms access, i had a feeling it was gonna barf

    could you save the subquery as a query please? the "AS x" subquery, save it and give it a name -- x works nicely because it's a cross join

    so then you will have --
    Code:
    SELECT x.SID
      FROM x 
    LEFT 
      JOIN [list - students] 
        ON [list - students].SID = x.SID
     WHERE [list - students].SID IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2008
    Posts
    163
    I made query "X" with the sql:
    SELECT codeyear & RIGHT('0000'&i10k,4) AS SID FROM codeyears, q10k WHERE SID <= lastused
    then I made query "fndmissing":
    SELECT x.SID
    FROM x LEFT JOIN [list - students] ON [list - students].SID=x.SID
    WHERE [list - students].SID IS NULL;
    Running either one of the two prompts me for an SID parameter. It uses the first character of whatever I type as a starting value for records to show (when I type gibberish like "hgrwe" it starts displaying records starting with "H" till the last existing record, when the first character is a number or the letter "A", it shows everything). The result is a single column (SID) with all missing numbers including those that reach 9999.

    I just realized that the q10k query also prompts for a parameter for q10k.expr1000 is that an error?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coffeecat
    Running either one of the two prompts me for an SID parameter.
    this means that query codeyears doesn't have a column called SID in its SELECT clause

    could you track that down please?


    Quote Originally Posted by coffeecat
    I just realized that the q10k query also prompts for a parameter for q10k.expr1000 is that an error?
    same here, track it down


    you could also paste the SQL here for either of those queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2008
    Posts
    163
    q10k:
    SELECT 1000*m.i+100*c.i+10*x.i+u.i AS i10k
    FROM integers AS m, integers AS c, integers AS x, integers AS u;
    codeyears
    SELECT LEFT(SID,4) AS codeyear, MAX(SID) AS lastused
    FROM [list - students]
    GROUP BY LEFT(SID,4);
    Thanks, I'm trying to see what I did wrong.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •