Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding missing numbers

    I'm working in Access 2007. I'm trying to find missing locations. If the location number goes from A1-01 to A1-04 I would like the report/query to give me A1-02 and A1-03

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    First all all, does the table you are looking at use "location" as the PrimaryKey? Or are you looking at a table that has a "location" field, but can have multiple records with the same location and you are trying to find which locations have no matching records in this table?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No the "location is not the primary key. Category,Contents, Location, and Storage date. I'm looking for any locations that might have got deleted in a append and delete query. There are over 14000 location spots. If we have boxes in A1-01 and in A1-02 and spots A1-03 to A1-10 are blink I want to be able to use that space.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    But how do you know what is missing? To know something is missing, you must have some idea of what is available. Do you have a master table of locations? If you do, then this is simple, it is just a query using an Outer Join.

    If you don't have such a master table, how could you possibly determine what is missing? In the example you have above, how do you "know" that A1-10 is missing? Is it possible you don't have an A1-10? Or is there automatically a 01-99 for each prefix? If so, do you have a master list of such prefixes? This would also make things easier.

    But if you don't even have that, then it gets messy. To start, don't even think queries for a second, how would you look at your list of records and determine a location is missing? You might start like this:
    - Sort the records by location.
    - Start at the first record, and assuming it is A1-01, you'd expect the next number to be A1-02. If it is, you are OK; if not, you've detected a missing number. But how many?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The warehouse locations are A1-01 to A1-55 up to A8-01 to A8-55; then "B" shelf starts and so on taking you up to Z1-01 to Z8-55. Every warehouse location spot is designed to hold a box. As boxes get taken off "is Null" for shredding that location opens up for another box. We refill all the "is Null" with new ones. If A1-03 to A1-10 does show up in the list "is Null or contains something in the contents field" then we have no idea that spots are available. I wish we did have a master table of locations. Thanks so much for trying to help with this

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 981 Times in 911 Posts
    To do it as a query I would expect you would need a location table with 3 columns, Index, Location, Used, but this would require a change to everything else. Otherwise you could extract all locations and drop them in Excel to find the gaps. (A new table would be better.)

    cheers, Paul

  7. #7
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Paul-that's what I did. Now I will have a master table of locations also. Thanks again

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    You need a master table of locations, but you don't need a "Used" field. Not only don't you need it, it is a BAD idea; you are dependent on this field being updated properly.

    You just need a query that basically looks like this:

    SELECT locations.locationID FROM locations LEFT JOIN boxes ON locations.locationID=boxes.locationsID WHERE boxes.LocationID Is Null

    Of course, I'm guessing at your table names, but the gist of this query is to return every record from your locations table that does NOT have a record in your boxes table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 981 Times in 911 Posts
    Yes, you don't want to update 2 tables when you use / clear a location - you can tell I don't code databases for a living.

    cheers, Paul (Jack)

Posting Permissions

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