Missing record query.

Yes jdraw I already use various queries that search by these fields, but I can't search for data in fields that don't exist, because they've been deleted!
 
cheekybuddha said:
You can try this query:
SQL: 
SELECT
  n.Disk AS MissingDisks
FROM Recordings r
RIGHT JOIN (
  SELECT DISTINCT
    t1.Num +
    t10.Num * 10 +
    t100.Num * 100 +
    t1000.Num * 1000 AS Disk
  FROM (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t10, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t100, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1000
) n
       ON r.Disk = n.Disk
WHERE n.Disk<= (
    SELECT MAX(r2.Disk) FROM Recordings r2
  )
  AND r.Disk IS NULL
;
Click to expand...
cheekybuddha said:
You can try this query:
SQL: 
SELECT
  n.Disk AS MissingDisks
FROM Recordings r
RIGHT JOIN (
  SELECT DISTINCT
    t1.Num +
    t10.Num * 10 +
    t100.Num * 100 +
    t1000.Num * 1000 AS Disk
  FROM (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t10, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t100, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1000
) n
       ON r.Disk = n.Disk
WHERE n.Disk<= (
    SELECT MAX(r2.Disk) FROM Recordings r2
  )
  AND r.Disk IS NULL
;
Click to expand...
Thanks, so busy writing I hadn't noticed the code. Should I cut & paste it into the criteria of a query under the 'Disk' field?
 
OK, you will perhaps need to adjust the query a bit to just use a list of unique disk numbers.

Create a new query and switch to SQL view. Then paste in the following SQL and run it:
SQL: 
SELECT
  n.DiskNum AS MissingDisks
FROM (
  SELECT
    Disk
  FROM Recordings
  GROUP BY
    Disk
) r
RIGHT JOIN (
  SELECT
    t1.Num +
    t10.Num * 10 +
    t100.Num * 100 +
    t1000.Num * 1000 AS DiskNum
  FROM (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t10, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t100, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1000
) n
       ON r.Disk = n.DiskNum
WHERE n.DiskNum <= (
    SELECT MAX(r2.Disk) FROM Recordings r2
  )
  AND r.Disk IS NULL
ORDER BY
  n.DiskNum
;

Whilst it looks like a lot of code, the repeating part of it (sub-query n) is just a query that will return a list of numbers from 1 to 10000.

This is limited to the maximum Disk number in your Recordings table and is joined to your list of distinct Disk numbers using a RIGHT OUTER JOIN.

You should just see a list of Disk numbers missing from your Recordings table.
 
Last edited:
cheekybuddha said:
OK, you will perhaps need to adjust the query a bit to just use a list of unique disk numbers.

Create a new query and switch to SQL view. Then paste in the following SQL and run it:
SQL: 
SELECT
  n.DiskNum AS MissingDisks
FROM (
  SELECT
    Disk
  FROM Recordings
  GROUP BY
    Disk
) r
RIGHT JOIN (
  SELECT DISTINCT
    t1.Num +
    t10.Num * 10 +
    t100.Num * 100 +
    t1000.Num * 1000 AS DiskNum
  FROM (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t10, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t100, (
    SELECT TOP 10
      (
        SELECT Count(o2.ID)
        FROM (
          SELECT DISTINCT
            ID
          FROM msysobjects
          WHERE ID >= 0
        ) AS o2
        WHERE o.ID > o2.ID
      ) AS Num
    FROM (
      SELECT DISTINCT
        ID
      FROM msysobjects
      WHERE ID >= 0
    ) AS o
  ) AS t1000
) n
       ON r.Disk = n.DiskNum
WHERE n.DiskNum <= (
    SELECT MAX(r2.Disk) FROM Recordings r2
  )
  AND r.Disk IS NULL
ORDER BY
  n.DiskNum
;

Whilst it looks like a lot of code, the repeating part of it (sub-query n) is just a query that will return a list of numbers from 1 to 10000.

This is limited to the maximum Disk number in your Recordings table and is joined to your list of distinct Disk numbers using a RIGHT OUTER JOIN.

You should just see a list of Disk numbers missing from your Recordings table.
Click to expand...
That's exactly what I'm trying to acheive. I'll give it a try. Thanks.
 
Woodpecker said:
Yes jdraw I already use various queries that search by these fields, but I can't search for data in fields that don't exist, because they've been deleted!
Click to expand...
The reason that Suggests --Don't delete the data from the database.
 
@Woodpecker I wasn't trying to belittle or suggest superiority.
Simply suggesting a better method of dealing with the problem/process moving forwards.

We know data and data-related processes (or at least we like to think we do), so trust me when I say that from experience deleting stuff is a last resort if you don't need to.
 
By empty slot, I'm assuming that you are referring to an empty physical location in a rack that holds CDs much like a numbered spot in a parking lot. The physical locations are labeled with permanent identification numbers but what gets stored there might vary from time to time. Think about the logic of it this way. If you park your car in 4A12, does that mean that you have to put a new label on your license plate? No, all that means is you need a way of finding your car when you need it.

So, you have CDs. Each CD has an ID number that is used as the PK in your table. An additional column in the table would be StorageSlot. So, when you search your table to find the content you want, one of the fields retrieved is the StorageSlot so you know where to find the CD. On the CD would be two IDs. One that Identifies the content and serves as the PK in your table and the second identifies the StorageSlot so when you are finished with the CD, you know where to store it.

This is not much different than what you have today. The difference is that you are using StorageSlot to not only identify the storage location but also to identify the content. Therefore, if the CD is damaged, you must delete the previous table entry to mimic the loss of the CD. I'm sure for your purposes,your solution is fine but if you wanted to keep the old data, your method prevents it. For my own use, I prefer the sanity check available by not deleting the old content because I'm likely to spend hours next year searching - I know that movie was here somewhere, why can't I find ito_O At least my search will always find it and I can see that the CD is marked as damaged and the StorageSlot is null indicating it has gone to meet its maker.
 
Thanks for your clear and concise reply Pat. What you say is perfectly correct and I empathise with your logic. If I were in a commercial environment where the historical integrity of the data was critical, then to a 'T', that would be the route I'd follow. However, as I appreciate you realize, this is a DB tracking details of a collection of recordings for my own satisfaction, some of which will be lost from time to time. I've trained myself over the years to follow a strict regime of accuracy so if a search doesn't reveal a particular recording I know I've removed it, - why, doesn't matter! Nobody but myself is relying on the data within.
I really do respect, and appreciate the effort you and all the contributors put into trying to 'pull me out of the hole', so thanks.
 
ebs17 said:
Off topic:
@cheekybuddha, the "lovers" of subqueries will surely be very enthusiastic about your suggestion.
Click to expand...
I only used a subquery to offer a single-step solution to the issue at hand.

The interesting part is being able to create a 'tally table' (a field of numbers) without creating an actual table and having to populate it.

You can achieve the same more cleanly with a couple of saved queries:
qryDigits: returns digits from 0 - 9
SQL: 
SELECT TOP 10 (
  SELECT
    COUNT(o2.ID)
  FROM (
    SELECT DISTINCT
      ID
    FROM msysobjects
    WHERE ID >= 0
  ) AS o2
  WHERE o.ID > o2.ID
) AS Digit
FROM (
  SELECT DISTINCT
    ID
  FROM msysobjects
  WHERE ID >= 0
)  AS o;
qryTally: uses a cartesian query to return numbers from 1 - 10000
SQL: 
SELECT (
  1 +
  t1.Digit +
  t10.Digit * 10 +
  t100.Digit * 100 +
  t1000.Digit * 1000
) AS Num
FROM qryDigits AS t1,
     qryDigits AS t10,
     qryDigits AS t100,
     qryDigits AS t1000
;

Then the solution will look much les intimidating!
SQL: 
SELECT
  n.Num AS MissingDisks
FROM (
  SELECT
    Disk
  FROM Recordings
  GROUP BY
    Disk
) r
RIGHT JOIN qryTally n
        ON r.Disk = n.Num
WHERE n.Num <= (
    SELECT MAX(r2.Disk) FROM Recordings r2
  )
  AND r.Disk IS NULL
ORDER BY
  n.Num
;
 
The interesting part is being able to create a 'tally table' (a field of numbers) without creating an actual table and having to populate it.
Click to expand...
In my databases, such a tally table is standard equipment because I can use it very often and in different cases to my advantage. There is no reason for me to recalculate something that is the same every time instead of calculating the result once and storing it in a table. Calculating takes time. The table also offers me the option of using an index.

My default helper table is called T999 because it contains the numbers 0 through 999. This is sufficient for most uses.
 
ebs17 said:
In my databases, such a tally table is standard equipment because I can use it very often and in different cases to my advantage. There is no reason for me to recalculate something that is the same every time instead of calculating the result once and storing it in a table. Calculating takes time. The table also offers me the option of using an index.
Click to expand...
Conversely, there's no need to have an extra table in your database if it's only used once or occasionally.

Calculating the above up to 10000 numbers is pretty instantaneous, and you can also have the flexibility to create it smaller or larger if fewer or more numbers are required. Otherwise, you have to keep an eye on whether there are adequate numbers in your table.

(I'm not disagreeing with you, I usually have a tally table and also an equivalent for dates in my dbs)
 
cheekybuddha said:
Conversely, there's no need to have an extra table in your database if it's only used once or occasionally.

Calculating the above up to 10000 numbers is pretty instantaneous, and you can also have the flexibility to create it smaller or larger if fewer or more numbers are required. Otherwise, you have to keep an eye on whether there are adequate numbers in your table.

(I'm not disagreeing with you, I usually have a tally table and also an equivalent for dates in my dbs)
Click to expand...
David, ebs17,

Isn't the idea of a tally table-whether a real table or a couple of saved queries- and an unmatched query (as I showed in #14) the same concept?
 
Sure, the reduced approach is 100 percent consistent, even with #3.
 
I have found it useful to have a table in my database, that contains a record for every integer from 1 to 100,000 with a field for the alphanumeric equivalent. With a table like that you can use an unmatched query to find missing values.

A table like that can created in Excel in a few minutes and then imported into Access.
 
You must log in or register to reply here.

Similar threads

D
How do I set up sequential numbering in one field of a table for each alike value in another field? I want to have this applied in a query.
Replies
13
Views
954
Pat Hartman
P
AOB
Solved Insert records where ID combination not already present (JOIN expression not supported)
Replies
2
Views
173
arnelgp
arnelgp
S
Getting total in one line of the query
Replies
13
Views
223
silentwolf
S
P
Long-running delete of duplicates
2
Replies
25
Views
989
CJ_London
C
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
373
ricardocpa
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom