Releationship XOver? (going from 1 to many to many to 1) Music dB

[willoughby]

Hello problem solving friends, of course I’m a pre novice and need all the help I can get. I’ve been constructing a dB based partially on ms template TS001018629.db. I only have three tables all with one to many relationships. Artists -> Recordings -> Tracks.



I’ve reached a point now where I see I erred at the start of my construction. I’ve come to the original soundtrack section where there are more Recordings than Artists (Recordings -> Artists -> Tracks). I know my mistake was by adding all my soundtracks as Artists.

Is there a way of opening Original Soundtrack and then Recordings and next Artists and last Tracks? With almost 43 hundred entries there are artists before the “Neil Young” entry that already have ArtistID’s and artists yet to come with no ArtistID #’s.

Out of the 9 songs I have from the “Animal House” soundtrack I have 6 Artists, and from “John Tucker Must Die” I have 14 songs with 13 artists. Of course I have a few soundtracks with just one song and others with only a few. I’m at a loss on how to proceed structurally? Any advice, help, hints I would be indebted.

Thanks
will

[orange]

You can get much more detailed if you want.

Here's a sample http://www.cs.rpi.edu/~sibel/dbs/lec...ic_example.pdf
Another http://www.databaseanswers.org/data_...tion/index.htm

A good tutorial on designing tables and relationships.

If you map out (pencil and paper) how the things fit together, and test it till you're happy, you could design a new database; then with some queries you could move from your current database to your new database.

[willoughby]

Thank you orange, however you may call me green with envy, or yellow in coward. I envy you for understanding the 1^st link you sent me. The 2^nd link is more understandable to me although I don’t want to have to change my whole dB.

I used to know queries but I’ve been away from access too long. You’ve given me an idea to go over to our comm.-col. where I 1^st worked with access and seek the help of a professor to help me map out my next step. If there were a way by using queries to change the structure I guess I’d give it a try. I’m very tired of cutting + pasting. Also it would be nice to connect all the artists that perform a particular song. I’ll bet I have 5 artists that perform ‘All Along the Watchtower’, and other artists that have the same song 3 times in their recordings(live, best of, and original). It would be nice to make that redundancy visible.

Any other help out there in the community and I’d be grateful. My last resort would be to visit the professors at our college.

-thanks
-will

[orange]

I only showed a few links after I googled "Msaccess data model cd dvd collection artist songs.."
I do not know your experience nor the details of WHAT you are trying to do. I did see a model/relationships window n your database.
The real issue as I see it is not one of queries. As with any database, if it doesn't meet the requirements of the "business", it isn't fully helpful. And most often the issue with not meeting requirements is that either
a) insufficient analysis up front or
b) requirements have changed since implementation.
But most often it is because the "developer" was a little too keen to use Access (or other dbms). I can assure you that developing a data model based on your requirements, and vetting/testing that model with test data and scenarios in advance of finalizing the model is one answer that works. Get the model to support the business, test the model, reconcile every anomaly...

It seems that your real data "can't be inserted in the database", right?

I'm not a music collector, but I can say you must distinguish the entities (read that as eventual tables), their attributes (read that as fields) and the relationships among/between entities/tables.

I see things in your post and the links like:
Artist, Song, Track, Album, Recording, SoundTrack, Track, Genre....
If you are going to retrieve info from the database along any or all of these lines, then they must be accounted for and tested in your data model.

Just a few thoughts to consider.