Create a normalised music database with Access

To create the Artist table we can run a Group By query (MakeArtist) that uses the DissectedData answer table and groups on the field called Artist.

If we also make this a ‘Make Table’ query, we can get it to generate a new table called Artist.

The SQL is:

SELECT Artist INTO Artist
FROM DissectedData
GROUP BY Artist
ORDER BY Artist;

The ORDER BY clause isn’t vital but it does make the tables neater. Open this new table (Artist), check the contents and add an AutoNumber field called ArtistID and make it the primary key.

To produce the Album table we need to extract the name of the album from DissectedData and tie it to the right artist in the Artist table.

We need to add the foreign key to the Album table. The query is called MakeAlbum and it generates a table called Album.

SELECT Album, Int([ArtistID]) AS ArtistIDs,
Artist.Artist INTO Album
FROM DissectedData INNER JOIN Artist
ON DissectedData.Artist = Artist.Artist
GROUP BY Album, Int([ArtistID]), Artist.Artist
ORDER BY Int([ArtistID]);

We are using the Int() function to change the AutoNumber field called ArtistID into an integer. I have also included the name of the artist (the Artist.Artist field) because we will need it in a moment.

Run the query and then edit the newly created Album table, adding an AutoNumber field called AlbumID and making it the primary key. Change the field name ArtistIDs to ArtistID. This also isn’t vital, but it is tidier.

Finally, we need to make the Track table. We have to be careful here because we could have albums and/or tracks with the same name.

To ensure each track is uniquely identified, in the query we join the DissectedData table back to Album on two fields ­ Artist and Album.

This query is called MakeTrack:

SELECT Int([AlbumID]) AS AlbumIDs,
TrackNo, Track INTO Track
FROM DissectedData
INNER JOIN Album
ON (DissectedData.Album = Album.Album)
AND (DissectedData.Artist = Album.Artist)
GROUP BY Int([AlbumID]), TrackNo, Track
ORDER BY Int([AlbumID]), TrackNo;

This generates a table called Track. Edit it and add a primary key called TrackID of type AutoNumber. Edit the name of the field called AlbumIDs to AlbumID.

Remove the Artist field from the Albums table. You should now be able to set up referential integrity.

That’s it. We started from a flat data file and have not only split it into three tables but have added the primary and foreign keys that keep them logically linked.

I’ve left the .wma extension on the end of the track names, but you can remove it using the query CleanTracks.

I’ve done some testing on this database and it appears to work, but don’t expect it to be commercial grade.

It also occurs to me the sample database could be modified to do all these operations automatically and create a database of the music held in Media Player complete with elegant forms, queries and reports.

If anyone wishes to take up the challenge I am happy to publish the best in a future issue. Go on, you know you love a challenge.