Linking Event Fields in one table from another table

[drums_4_life]

I have a two tables in my database
One table is a list of song titles, tempo, key signature, and author.
The other table is a list of specific dates and with those dates it has song title, tempo and key signature.

How do I make it so that the second table's "Song Title, Tempo, and Key Signature" is linked to the first table's "Song Title, Tempo, and Key Signature?"

I would like to be able to pick a song title in the second table and have the tempo and key signature automatically fill in to match the song. I attached a photo to kinda explain please feel free to ask more questions if i did not explain it very well. Thanks for the Help.

 

[Mr Smin]

Looks like you want a table of all the music in your collection, then another table of all the occasions when each piece of music has been performed.

You could do that by linking your table of unique pieces of music to a table with just a date column and a MusicID column. The MusicID column is the primary key of the music table and a foreign key in the dates table.

It could look like this
Date -- MusicID
28-Mar-2011 -- 1
28-Mar-2011 -- 9
28-Mar-2011 -- 8
10-Apr-2011 -- 8
10-Apr-2011 -- 12
10-Apr-2011 -- 14

The number on the right correspond to items in the music table and a query can let you see the key, tempo etc. without having to store the same thing (key, temp etc) in more than one table.

 

[drums_4_life]

Thank you for that response. I understand its a pain to store the same thing in more than one location but for what i need i believe that is the only way i can do it.

Here is a picture of my form.

So at the top of this form I am able to search by service date, when I pick a date i would like the subform to display the song title, tempo and key for each song so I can make a report of that subform and print it. I have seen one database that can do that but with different information. Does anyone know how to do that?

Thanks All