Results 1 to 10 of 10

Thread: Access Query

  1. #1
    Join Date
    May 2009
    Posts
    3

    Smile Unanswered: Access Query

    Hello,

    I have a query as per below - Query name - Seq_Q

    Results are

    Seq CountOfSeq
    12404 234
    12405 23
    12406 10
    12407 13
    12409 134

    and this continues for 10,000 rows

    How can I find out via a query on this query if there is a number missing. eg in the above case where 12408 is missing.

    Thanks for the help
    Cheers
    Peter

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "Via a query" you would have to have a table with all the valid sequence numbers in - Then have a query joining your table from the Sequence Field in your table to the sequence Field in the tblValidSequences. The query would have a WHERE tblMyTable.SequenceNumber IS NULL.

    You may prefer to use a recordset to check which ones are missing via code.

    If you need further assistance or do not understand - let us know.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    +1 for the numbers table - these are handy for so many things.
    -1 for the VBA recordset - slow and unnecessary

    Simplest and easiest:
    Code:
    SELECT t1.*
    FROM t AS t1
    LEFT OUTER JOIN
    t AS t2
    ON t1.Seq +1 = t2.Seq 
    WHERE t2.Seq IS NULL
    It'll give you one false positive, but I assume you can handle that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Poots

    LOL - Thanks for the marks! 10/10 for that!

    Great suggestion though - don't really use sequence numbers myself but very handy!



    PS There is a radio advert on the moment which keeps playing your theme tune - doing my head in!!!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by garethdart
    PS There is a radio advert on the moment which keeps playing your theme tune - doing my head in!!!
    Badap Badap Badap ba baaaaaa! Awesomeness

    BTW - the code will need running iteratively - it there is a gap of two, then it will only return the lowest of these two numbers. The numbers table approach, however, would return every missing number.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Baaa badap badaa!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    May 2009
    Posts
    3

    Smile Access Issues

    Thanks for the help. I've tried to run

    SELECT t1.*
    FROM t AS t1
    LEFT OUTER JOIN
    t AS t2
    ON t1.Seq +1 = t2.Seq
    WHERE t2.Seq IS NULL

    But it comes back with "mismatch in expression"

    Original Table - Is now called T and it contains 3 columns,
    Column 1 = Seq, Column 2 = Name & Column 3 = Date
    All I'm worried about is trying to find where there is a missing file in the Seq column.
    I want to be able to run a query that shows me what numbers are missing
    eg Column 1 = Seq

    10141
    10142
    10143
    10148

    I need the result to show me the numbers that don't appear. This will then allow me to investigate the missing files.

    Thanks again for your help, I really appreaciate it!!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is Seq a text column? And if so, why?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Indeed - worked fine on my test table using an integer...

    ....Over to you Peter.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    May 2009
    Posts
    3
    Thanks, works pefectly when not using text, have a great day!!

Posting Permissions

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