Two Key fields

G

Guest

I have a songlist database with over 100,000 songs listed by artist, title,
disk# and track#. There are many duplicate songs by many labels with the
same artist and title. I'm trying to create a songbook with out duplicate
songs by the same artists. I want to be able to list all the songs by all
the different artists with out having duplicates. To further explain, if I
assign the title field as the key, I will get all titles but only by one
Artist not by all of them. Its like I need two key fields with unique
values. I've been trying different filters and querys but just can't figure
out the proper procedure. Please help.
 
M

Michael Gramelspacher

I have a songlist database with over 100,000 songs listed by artist, title,
disk# and track#. There are many duplicate songs by many labels with the
same artist and title. I'm trying to create a songbook with out duplicate
songs by the same artists. I want to be able to list all the songs by all
the different artists with out having duplicates. To further explain, if I
assign the title field as the key, I will get all titles but only by one
Artist not by all of them. Its like I need two key fields with unique
values. I've been trying different filters and querys but just can't figure
out the proper procedure. Please help.

SELECT SongList.song_title, SongList.artist_name
FROM SongList
GROUP BY SongList.song_title, SongList.artist_name;
 
G

Guest

You don't say how the data is structured, but it sounds like you might have
the one table. If this is the case it should be decomposed into three
tables, Artists, Songs and a third, Recordings say, representing the
many-to-many relationship between them. This eliminates redundancy as each
song or artist is entered only once, and prevents inconsistencies arising,
e.g. the same artist or song being entered slightly differently. It would be
advisable to use numeric primary keys, ArtistID and SongID for the Artists
and Songs tables as there could be more than one artist of the same name and
similarly more than one song of the same name. These can be autonumbers
columns for convenience.

The Recordings table would have columns ArtistID, SongID (not autonumbers in
this case), Disk# and Track#.

The tables can be filled from the existing data by three 'append' queries,
but this does assume that at present all artist and song names are unique
(i.e. no two artists or songs have the same name or title) and consistent
(the same name or title has always been entered in exactly the same way).
First you'd append DISTINCT artist names for the existing table to Artist,
then do the same with the song titles into Songs. Next join Artists and
Songs to the existing table on the artist and song title text columns and
append ArtistID, SongID, Disk# and Track# from Artists, Songs and the
original table to the Recordings table.

You will now have a set of correctly normalized tables and can return a
songbook via a query which joins all 3 tables and SELECTs DISTINCT artistID,
artist, songID and songtitle values form Artist and Songs (the recordings
table is only in the table to provide a join between the other two, so no
columns from this need be returned). Including the ID columns in the query
means that two or more artists or songs with the same names or titles but
which are in fact different people or songs will be treated as separate.

A report based on this query will give you the song book. The report can be
grouped by artistID or by songID, depending on whether you want to lay it out
artist by artist with the songs by each, or song by song with the artists
who've recorded each. If the former group first by artist name then by
artistID, but give the latter a group header, not the former, and include the
artist name in the header and the song titles in the detail section. This
will mean the artists are shown alphabetically by name. Do similarly if
grouping by song.

Ken Sheridan
Stafford, England
 
M

Michel Walsh

You can have a primary key made of more than one field: In the design view
of the table, select BOTH fields (Ctlr-click could be use to add/remove
items to a list of selected items, in Windows), then, click on the 'key' in
the toolbar to make the selection of two, or more, selected fields the
primary key. No duplicated 'couple' (or triple if made or three fields, or
tuple, in general) would be allowed in your table. Note that is there is
already duplicated couple, the primary key will refuse to work, since it
won't know which (duplicated) row to eliminate.

It is not an obligation to be a primary key, though, it can be made as a
standard index (on more than one field) not allowing duplicated values. For
an index, that is different: open the indexes view, define a new index by
giving a name to the index (new row, first column). Give a field for the
second column, same line. At the next line, don't add anything as name
(first column) since we continue the definition of the index, we don't
create a new one, and, in the second column, supply the second field name to
be used. At the bottom of the indexes sheet view, there is a property about
allowing duplicated values (of the couple) or not. Change it as appropriate
(hey, you can get duplicated couples, if that is what you want, but to do
it, you use an index, not a primary key).



Hoping it may help
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top