Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: Find Missing Numbers In A Sequence

    I have a table which contains the sum total of all the files in my media library. Each file has an Artist, Album, Disk Number and Track Number. The Track Numbers fall in sequence from 1 To X depending on how many tracks there are on a given disk. Can anyone suggust a query that would return the Ariist Album and Disk Number where the track numbers are not in sequence, ie, a missing track?

    I have serveral thousand MP3s in my library and I'm looking for a faster way to find missing information in my source table before I port the data to my database. The source table was extracted from the XML file used by iTunes. I used a XML Shreader to import the data into Access. While I found the program very helpful. I don't have complete faith in the output, as I already know that the first track from the first album in the file is missing.
    Last edited by VSCurtis; 10-21-14 at 18:48. Reason: Added Additional Thoughts

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Erm no, your track numbers go from ' 1' to 'nn'

    It should be easy enough to identify albums whoose COUNT of tracks is not equal to the MAX track for that album. Whether the MIN and MAX functions will work on text data I dunno. Perhaps your tutors could advise

    Having identified those albums you could then JOIN to a numbers, sorry text table (runninf from ' 1' to whatever is the highest expected track reference, say '30'. Selecting all values from the numbers table and tracks. As ever its down to the where clause. In that where clause you only want numbers that are lessthan or equal to the highest trackid for that album. As you want to find missing numbers in the sequence you want to use a left join and so ypur whete clause should only allow rows whose track name is null.

    You may need to do thus as several queries. Bear in mind that Access can treat a query as a table in other queries
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2014
    Posts
    48
    Thanks for the suggestion. Why does everybody here always have to be such a bunch of smug idiots? And there not my tutors they were my instructors at DeVry University. Where did you goto college smart mouth? How many degree do you have? What was your GPA?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    GPA dunno,dont know what it is
    but lets just say qualfications: plenty.

    The reality is you've been given good advice from people who do as opposed to people who teach. Storing the track number as a string is a bad idea. Now I recognise its your design decision not your tutors, but its still a poor decision (in my books)

    In the absence of table design and sample data its impossible to give a better worked response. Sorry if the response was verbose. Now if you believe people here are smug idiots, well thats fine. If you cannot take a bit if ribbing then thats fine. Ill try and remember that when or if there is a next time.

    In the meantime how far have you got resolving the problem
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2014
    Posts
    48
    My point is simple. If someone came to me and asked for my assistance I would give it without being smug about it. I would offer my views and let them decide. I would not belittle them. I have not yet tried your suggestions. I'm taking a few hours away to clear my head before I proceed. I do intend on doing so since I found them helpful. If you would like to see what I've come up with I'll be happy to provide it after I've ported the data to this last table. Thank you for your help.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Review a few thousand posts (a tiny fraction of what healdem has responded to, much less read) and you'll find a recurring theme of people with a sense of entitlement and very high expectations.

    healdem did assume that you're working on an assignment for a class, but I would have made the same assumption based on what you posted and how you expressed yourself.

    Keep in mind that healdem is offering free advice in a professional field. You're free to use or ignore that advice as you see fit. You made a poor first impression, but I don't know that any of the DBForums regulars will hold that against you. We've all made mistakes (I know that I've made some doozies) and if you contribute to the forums you'll find that people will treat you with respect.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2014
    Posts
    48
    I understand your points and I appreciate being addressed in a professional manner. My project is a personal project not work related. I do appreciate people's thoughts and suggestions and I DO listen and even understand their points of view, but I dont have to agree with them and I see no reason for anyone to be smug or belittling of someone else. I try to be professional and couteous to others. I dont react well to smug arrogant people who think that their way is the only way to do something. I appologize if I have offended anyone.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    revisiting this Im not too certain I can see a way of identifying specific track numbers that are missing
    ..but identifying albums whose number of tracks does not match the highest track number is relatively easy

    a way of doing this is:-
    find the maximum and count of tracks per album, from that you can see the discrepancy

    Code:
    SELECT 
      Abums.Title,
      Abums.ID,
      Max(tracks.trackno) AS HighestTrackNo,
      Count(tracks.trackno) AS NoTracks,
      Abs(Count(tracks.trackno)-Max(tracks.trackno)) AS discrepancy
    FROM Abums
    INNER JOIN
      tracks ON Abums.ID = tracks.AlbumID
    GROUP BY Abums.Title, Abums.ID
    HAVING Abs(Count(tracks.trackno)-Max(tracks.trackno))>0;
    the discrepancy is the ABSolute difference between the highest track number and the count of tracks by album

    if your data allows nulls as the track number then you may have to use the NZ function to handle that

    this query will not identify albums where there is a duplicate tracknumber AND the count of tracks happens to match the highest track number identified

    The base problem is that you don't control the data. in my squeezebox library I have quite a few track whose track number is 0, sometimes all track for an album are 0, I also have a few albums which are duplicated (at soem point the CD was re ripped using a different path or a directory has been moved about. That may well be an artifact of the way I've ripped CD's over time, and the way the digital library structures and process have changed. Its also not helped by me converting my LP's into CD's and then ripping those. iTunes may do things differently, and if you have only ever ripped cd's using the same tool and the same settings you will probably be allright.

    although a numbers table usually 'is' the way forward in this situation I can't see a way of using it if you have separate tables for Album and Tracks. if the album and track were in the same table then I cant see a way of reporting what album has a missing track AND identifying that track.

    there may be a way of doing this but right now Im stumped
    Last edited by healdem; 10-23-14 at 15:30.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2014
    Posts
    48
    Thank you very much for your efforts to help me resolve this problem. It was not my intent for you to goto such efforts merely to give me some food for thought and you did that. Thank you again.

Posting Permissions

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