# Read me

comment:
2nd version of database, with m:m relationship between track and artist. Use the Set form to enter/view details.


comment:
I have now used AutoNumber ids on all tables - makes joins, list/combo boxes easier.


comment:
Main form (Set) is rather messy, trying to show multiple artists - also messy adding artists.


comment:
Possibly trying to put too much on one form? I've experimented with different shades of grey for the 3 forms - not sure that I really like the result…


comment:
Tracks now show in track order in the Set form - see comments in track form code module


comment:
Has basic three tables from version 1, now plus appearance and artist, making 5 tables:


comment:
Disk - each set has 1 or more disks, each disk belongs to only 1 set, 1:m relationship


comment:
Track - each disk has 1 or more tracks, each track belongs to only 1 disk, 1:m relationship


comment:
This is how the main form (Set) was created:


comment:
first I created the Track form (using Wizard), a tabular form listing all tracks.


comment:
then I created a columnar Disk form (Wizard), and inserted the track form as a sub form.


comment:
finally I created a columnar Set form (Wizard)…


comment:
…and inserted the Disk form as a sub form within the Set form (it worked! I now know you can have subforms within subforms).


comment:
then much messing about to get the forms to display in a vaguely reasonable fashion.


comment:
The various forms do not need to show all key nos., but, while testing at least, it is reassuring to see that the right details are being picked up.


comment:
To get rid of the 'new record' line at the end of a tabular form (Track and Artist), set 'allow additions' property for the form to 'No'.


comment:
Simple report to list all CDs in title order - using Wizard then moving stuff around for better layout.


comment:
A further refinement may be to have a title table, but this depends how often a title is repeated. The data here has two tracks for 'Hound Dog' - try the title query using 'dog'.


comment:
Appearance - simply shows track-id and artist-id, to resolve m:m relationship between track and artist.


comment:
Artist - for artist name - optional - if the name is also in the set title then there is no need to enter artist for each track in that set. See also points 4 and 5 below for coping with queries and reports where the artist name is optional.


comment:
As the artist details are not required to be entered for each track, the normal query method will not select records for which there is no artist shown.


comment:
You need to specify right/left joins when doing queries that involve the artist name table. See the FAQ page of COMP1003 for an explanation of these. See the various queries here for examples.


comment:
Note that the artist name query checks both the set title and the artist name table for the required name. Try entering 'Nina' and 'Elvis'.


comment:
Artist - an artist may appear on one or more tracks, and each track may have one or more artists - thus m:m relationship.


comment:
Note that the queries work using LIKE with wildcards ("*") to pick up the parameter value anywhere in the search field.


comment:
Look at the code module for this report to see how blank lines (where there is no author name for the track) are suppressed.


comment:
I have tried to do as much as possible using Access standard features, and a minimal amount of VBA (only used where Access features not adequate). Result is not foolproof and HCI could be better.


# Set

CD Details:
M&S Compilation - "An evocative introduction to the blues. Original artists, original recordings".


CD Details:
Triple CD set celebrating 100 years of great Jazz. EMI.


CD Title:
Vier Letze Lieder (Richard Strauss), Jessye Norman


