Creating a Query

[Funky Monk]

I am developing a database of songs. I have given each song a Theme as I want to be able to search through the database and find all songs that match Theme 1. I would then like to be able to add a second Theme to each song and then search for all songs that meet the first criteria or the second. For example,

Table 1:
Holiness
Grace and Mercy
Peace (etc)

Table 2 is the same, so that song 1 could be about Holiness and Grace (in Table 1) and Mercy (in Table 2).

I have created these tables and a basic Query, but this only allows me to search for records from 1 table. In SQL View it is:

 SELECT tblSongTheme.Theme, tblSongs.[First Line]
FROM tblSongTheme INNER JOIN tblSongs ON tblSongTheme.ID = tblSongs.Theme
WHERE (((tblSongTheme.Theme)="holiness"));

I hope you follow what I am trying to say.

[MMcCarthy]

Set up tables as follows:

tblSongs (List of Songs)

SongsID
SongName
FirstLine
Artist

tblThemes (List of Available Themes)

ThemeID
Theme

tblSongThemes (list of Songs and Themes by ID)

SongsID
ThemeID


Now create a form with tblSongs as its record source.
Create a Subform with the following query as its record source.
Use SongID for the parent child relationship.

 
SELECT tblSongThemes.SongsID, tblThemes.ThemeID, tblThemes.Theme FROM tblSongThemes INNER JOIN tblThemes
ON tblSongThemes.ThemeID=tblThemes.ThemeID

You can now create your query to search for songs as follows:

 
SELECT tblSongs.SongsID, tblSongs.SongName, tblSongs.FirstLine, tblSongs.Artist
FROM tblSongs INNER JOIN (tblSongThemes INNER JOIN tblThemes
ON tblSongThemes.ThemeID=tblThemes.ThemeID) 
ON tblSongs.SongsID=tblSongThemes.SongsID
WHERE (tblSongThemes.Theme IN ('Holiness', 'Grace and Mercy');