Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Missing Transmittal Report

    Dear Seniors,

    I have a Transmittal database where the list of Transmittals are entered in 1 Table.

    Like
    TR-3301
    TR-3302
    TR-3303
    TR-3305
    TR-3306
    TR-3308

    Now in the above list I have some missing Transmittals say TR-3304 & TR-3307. Is it possible to extract report which shows the missing Transmittal Numbers? I have in my database approximately 10000 Transmittals
    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you know 3307 and 3304 are missing? (in terms of the system design, not a human reading a list)

    what you could do, in the absence of a register is create a numbers table. Im guessing that the TR- is in all rows and is in effect superfluous.....

    This will report any single missing Transmittal number. ie it will report only ne missing number, if there is more than one missing number it will only report the last one. say your numbers were 300,301,302,305,308 then it will report 299,304 and 307, NOT 299,303, 304 306 and 307
    Code:
    SELECT Left(T1.TXID,3) & Format(Mid(T1.TXID,4)-1,"0000") AS MissingTXID FROM MYTABLE AS t1
    LEFT JOIN  MYTABLE AS T2 ON Left(T1.TXID,3) & Format(Mid(T1.TXID,4)-1,"0000")  = T2.TXID
    
    WHERE ISNULL(T2.TXID)
    change mytable to the name of your table, txid to the name of your column
    the prefix ("TR-" is required
    the number of columns in the format. if you ONLY have 4 digits then its fine as it is
    its a pretty ugly bit of SQl, but then again you don't make life easy for yourself by stroing redundant data (the TR- bit) and as a result use the wrong datatype for the resultant number

    if you used a numeric datatype
    Code:
    SELECT T1.TXID-1 AS MissingTXID FROM MYTABLE AS t1
    LEFT JOIN  MYTABLE AS T2 ON T1.TXID - 1  = T2.TXID
    
    WHERE ISNULL(T2.TXID)


    off hand I cant think of another way of doing this except for using a numbers table and using the mismatched query wizard

    that woudl involve a table with say TR-0001 to TR-9999 and then join to that table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Run this code to fill a table called tAll that has EVERY possible number between your smallest and largest in you main data table.
    Then it opens an outer join to show you what is missing.

    Code:
    vStart = dmin([Xnum],Table)
    vEnd = dMax([Xnum],Table)
    
    docmd.openQuery  "qdEmptyAllTbl"    'empty our all number tbl
    
       'populate the all table with EVERY NUMBER
    for i = vstart to vEnd
       sSql = "Insert into tAll ([xnum]) values ("TR-" & format(i,"0000"))
       docmd.runsql sSql
    next
    
    'RUN AN OUTER JOIN TO DETERMINE WHAT IS MISSING
    DOCMD.OPENQUERY "qsMissingInTbl"

Posting Permissions

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