By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,066 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,066 IT Pros & Developers. It's quick & easy.

Creating a Query

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1.  SELECT tblSongTheme.Theme, tblSongs.[First Line]
  2. FROM tblSongTheme INNER JOIN tblSongs ON tblSongTheme.ID = tblSongs.Theme
  3. WHERE (((tblSongTheme.Theme)="holiness"));
I hope you follow what I am trying to say.
Oct 24 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT tblSongThemes.SongsID, tblThemes.ThemeID, tblThemes.Theme FROM tblSongThemes INNER JOIN tblThemes
  3. ON tblSongThemes.ThemeID=tblThemes.ThemeID
  4.  
  5.  
You can now create your query to search for songs as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT tblSongs.SongsID, tblSongs.SongName, tblSongs.FirstLine, tblSongs.Artist
  3. FROM tblSongs INNER JOIN (tblSongThemes INNER JOIN tblThemes
  4. ON tblSongThemes.ThemeID=tblThemes.ThemeID) 
  5. ON tblSongs.SongsID=tblSongThemes.SongsID
  6. WHERE (tblSongThemes.Theme IN ('Holiness', 'Grace and Mercy');
  7.  
  8.  
Oct 24 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.