Newbie : many2many advice needed...

[opopanax666]

Hi guys & gals,

First off, I'm pretty good with Excel, but a total newbie to Access. I believe in steep learning curves, so to get to grips with Access, I'm trying to understand the logic behind a M2M database. But I simply can't seem to work out what's involved .
Let's paint the picture : I want to make a CD-database including compilations, hence the M2M. I already had a Excel-sheet, which I tweaked a bit to comply with the requirements of a M2M, i.e. columns for ID (for the key), AlbumID (my old ID system that I want to keep), ReleaseDate, Title, Artist, Genre, Publisher and Notes. For a compilation I didn't make different columns for the artist, but gave each artist it's own row (I figured that much).
The problem now is figuring out the procedure to follow to import this sheet in Access, and then implementing the database. I've been looking on the net, but the info I find is on starting a new database, which doesn't apply.
Does anybody know of a good tutorial?

Thanks for any feedback

 

[gemma-the-husky]

if you have a single spreadsheet, you can import the data as is. Access has a utility which will attempt to split your tables for you.

This was called tableanalyzer in A97, not sure in later versions, but search for normalize in help, and Access will have a way to split it for you, or at least make a start!

 

[opopanax666]

First, thanks for the reply. Second, this is a learning experience. The result is not important (I actually don't need a CD-database), it's how to get there I'm interested in. And third, I've got the 2003 edition, and this analyze thingy doesn't recognize a M2M.
To recap, I don't want a CD-database, I do want to learn how to make one ;-)

 

[gemma-the-husky]

the idea is to normalize the tables, so you split data into logical sections

eg with a cd database you have a table for

CD including things like (CDNo, title, artist, publisher, date recorded etc)
you assign a unique ref to CDNo, for your internal use

you have another table called tracks
Tracks (CDNo, TrackNo, TrackTitle, TrackDuration, SongWords, etc)

the only link between these two tables is the cdno, so you do not duplicate data in the tracks table that you can get from the cd table.

By the same token, you don't store in the cd table, the cd time length, no of tracks etc, because you can get these by reference to the tracks on the CD.

(Note however that the demo shipped with Access fiddles this, and does actually store the totals with the CD!)

You probably need to extend this to have another table for artist, so you can link your cd to artist. In the artist table you can then store things like DOB, Place of Birth, Biographical Notes etc.

Where this then starts to get hard is when you get an artist that has been in multiple groups, or where you have compilation CDs with different artist.
So at this point you have to think very carefully about how YOU want to structure this data, to retrieve the particular information you want from the database. eg, you may not be bothered that Phil Collins was also in Genesis and Flaming Youth (I think!), for the purposes of your CD database.