Topic: Retrieve tracks of a Musical CD

Hello to all

I'm working on a music cd collection manager.
I use several tables, one of which stores the tracks and their duration (so as not to overload the main table (with artist, name of the album, musical style, etc.).
When I consult any artist, I would like to list the tracks belonging to this or that album of this artist. (Behin OnCellClick from the main Tablegrid)

What is the correct SQL syntax to achieve this?
Is it a good idea to save the tracks (and their duration) in a dedicated table having a relationship with the main table?

Thank you for your leads and your ideas on this topic

Best regards

JB

Re: Retrieve tracks of a Musical CD

Hello Jean,


What I post here is a solution, but not the only one.
The database structure approach is very "standard" where I usually tend to put as many information as I can in one table and use trigger nowadays.


Anyway, I'm quite proud of the track duration handling, and the calculation to sum all albums tracks in minutes and seconds smile


Have a good day old friend


Cheers


Mathias

Post's attachments

Attachment icon CD_Compiler.zip 334.96 kb, 443 downloads since 2018-03-06 

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: Retrieve tracks of a Musical CD

Hello mathmatou

Thank you for your approach
I retain the idea of using a SQL Query.
I remember especially the calculation of the duration, I arrived via the fields calculated with a conversion routine for minutes. What calculations!
To fill the band table, you probably used a button that you later deleted?

Thanks

JB

Re: Retrieve tracks of a Musical CD

Hello Jean,

No, the missing Button1 was a attempt at creating a search button instead of a SQL Query button, but the total duration of each album was not correctly calculated by the calculated field so I changed my mind.


The groups, albums and tracks data was filled... by hand directly into the database smile


Math

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: Retrieve tracks of a Musical CD

Hi Jean, Hi Mathias,
I didn't see your post so I'm not sure if you are still looking for ideas.
I too wrote a small music management app a while ago.  Its data structure is very simple -  a table for artists, a table for CDs, a table for tracks, with the obvious relationships between the 3.
Although the data structure is 'top to bottom' hierarchical (ie search from the artist down to CD down to track), I also wanted to be able to select by track and have it search back up the hierarchy - and also select by CD and have it search both up and down the hierarchy (I hope that makes sense!).
It also needed to display tracks alphabetically when searching by artist but sort by track number once a CD has been selected.
However, it doesn't try to calculate the duration of a CD (it wasn't a requirement) but I may 'steal' Mathias' script and update my program!
Anyway, I have attached it and maybe it will be of some help (the data was just for testing purposes - it's not my 'true' tastes in music - honest!!)
Regards,
Derek.

Post's attachments

Attachment icon Music Maestro.zip 360.94 kb, 484 downloads since 2018-03-09 

Re: Retrieve tracks of a Musical CD

Hello Derek

Very good your approach !

By mixing your project with that of Mathmatou, I should get something perfect

I'm waiting for Dmitry to finish implementing the TreeView properties to make a little gem

Thanks again for this project

Best regards

JB