Missing record query.

[Woodpecker]

Good morning, I use Access 2000 to store details of recordings on DVD discs. There are well over 1000 discs on file and sometimes a disc may get damaged. These, I physically remove from the collection and also remove the record from the Recordings DB, which leaves gaps in the collection where those discs once lived. I would like to be able to run a query that identified positions in the file where the disc numbers are non sequential, ie. where a disc has been removed. Could anybody please suggest an expression criteria that would acheive this? Thanks.

 

[Minty]

I wouldn't delete the data, for one thing, you might get a replacement, and wouldn't you rather know what you had removed?

Add a field for DateRemoved, then populate it when you remove something.
Then you can exclude those records with removal dates from your normal records, or show them if you want to see the history.

 

[ebs17]

SELECT
   A.Number
FROM
   tblAllNumbers AS A
      LEFT JOIN tblDiscs AS D
      ON A.Number = D.DiscNr
WHERE
   D.DiscNr IS NULL
      AND
   A.Number >=
      (
         SELECT
            MIN(DiscNr)
         FROM
            tblDiscs
      )
      AND
   A.Number <=
      (
         SELECT
            MAX(DiscNr)
         FROM
            tblDiscs
      )

 

[Woodpecker]

I wouldn't delete the data, for one thing, you might get a replacement, and wouldn't you rather know what you had removed?

Add a field for DateRemoved, then populate it when you remove something.
Then you can exclude those records with removal dates from your normal records, or show them if you want to see the history.

No Minty, I don't need to know what was removed, or when.

 

[Woodpecker]

SELECT
   A.Number
FROM
   tblAllNumbers AS A
      LEFT JOIN tblDiscs AS D
      ON A.Number = D.DiscNr
WHERE
   D.DiscNr IS NULL
      AND
   A.Number >=
      (
         SELECT
            MIN(DiscNr)
         FROM
            tblDiscs
      )
      AND
   A.Number <=
      (
         SELECT
            MAX(DiscNr)
         FROM
            tblDiscs
      )

Thanks, but there's never a Null value in the Disk field. ie. In the attached screengrab, it shows that I've deleted the faulty disk number 488, and can now replace it with a new disk, and new titles. I just need to be able to quickly locate these missing disks.

 

[cheekybuddha]

It would be much easier to offer a suggestion if you described your table for us: field names and datatypes, and highlighting fields of interest.

 

[Woodpecker]

Hi, attached is a screengrab of Recordings Table. The datatype of the Disk field is highlighted as in this instance, I think it's the only field that's relevant?

 

[ebs17]

I really don't care what you do in detail.
Because the following rule applies: Missing data records are determined by comparing the existing set against a complete set of keys.
A rule can be applied creatively and responsibly.

 

[jdraw]

It appears that this table does not have a primary key.

In #1 you say "I would like to be able to run a query ....... where a disc has been removed", but in response to Minty
"No Minty, I don't need to know what was removed, or when."

 

[Minty]

The point of my post (along with @jdraw observation) is that you have nothing to gain and everything to lose by deleting the records.
Storage is cheap, even if you had 500 deleted records out of 10000, it is such a small amount of data you won't notice the difference, but you will have a problem when it comes to finding the missing numbers.

If you had marked the record with a removed date this would take about 30 seconds to provide you with a list of removed disc numbers.

 

[cheekybuddha]

You can try this query:

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
;

 

[Gasman]

Why do you feel the need to reuse the disk number?
You can count how many you have easy enough any time you want to know how many you have.

 

[cheekybuddha]

Also note that your Disk field allows duplicates - so, unless you are very careful with data entry, it's possible you may have more than one film per disk number

 

[jdraw]

I agree with Gasman. What exactly is the significance of sequential Disk numbers?
And Minty--identify the record when removing it.
If you insist on sequential numbers, then this approach(following sql) would identify missing sequential numbers.

Condition: You have a tblNumbers with 1 field Num populated 1 to some maximum.

Then, an unmatched query, with tblNumbers and "your recordings table"

SELECT tblNumbers.*
FROM tblNumbers LEFT JOIN MockRecordingsT ON
                  tblNumbers.[Num] = MockRecordingsT.[disk]
WHERE MockRecordingsT.disk  Is Null and
               tblNumbers.Num <= (select max(disk) from MockRecordingsT);

will list the missing MockRecordingsT.disk values.

 

[Woodpecker]

I clearly, haven't described my problem very well. I also realize that I'm communicating with you and others who have a far greater depth of experience in Access programming than myself. Let me describe the practical side of what I'm trying to acheive. I'm a retired engineer with no background in IT, who has various collections at home that have to be catalogued. The DB in question holds information on video recordings that I've burnt to disk. Occasionally, I may attempt to play a disk that has become unreadable, so I bin it. I also delete all records pertaining to the recordings on that disk. The physical disk file now has an empty slot which can be reused when I burn another disk. Because that may not be immediately, empty spaces can accrue in the physical file. When I get round to re-using these empty positions I have to spend time searching for empty spaces, or laboriously scanning ninety pages of print. The recordings on these disks are not critical data, so I don't need them to be traceable but just locatable. I was hoping that using the right expressions in a query criteria would quickly list the spare places in the disk file but if that isn't the case, I'll just carry on as before without further wasting anybody's time.

 

[cheekybuddha]

So did you try the query I suggested in Post #11?

 

[Woodpecker]

Also note that your Disk field allows duplicates - so, unless you are very careful with data entry, it's possible you may have more than one film per disk number

I do always. A disk can hold up to 100 separate recordings, though in practice the average is 3 - 4.

 

[cheekybuddha]

So a disk number is likely to appear multiple times in the Recordings table?

 

[Woodpecker]

So a disk number is likely to appear multiple times in the Recordings table?

Yes, it will appear on every record relating to that disk.

 

[jdraw]

You aren't wasting people's time. We are all unpaid volunteers whose interest is in Database, MsAccess and assisting others --- especially those with a specific requirement who are new to database.

"laboriously scanning ninety pages of print." -why exactly when you have a machine readable database.
Why not an Access-based form with search capability. Search by Genre, Director, Disk..... that is what digital databases do.