Many to Many query for web search

[Penny]

Hi All,

I've made a database for a record store and am currently attempting to make
it 'Web Searchable'. An Artist can be associated with many Recordings and
vice versa so there is a 'tblArtists' with an ArtistID, ArtistName and a few
other artist details, a 'tblRecordings' with a RecordingID, RecordingTitle
and a few other recording details and also a 'tblLINKArtistsAndRecordings'
with only the 'ArtistID' and 'RecordingID' composite primary key to link it
all together. Works well.

How do I create an SQL string that will take the name of an Artist(entered
as a 'keyword' by the web page user) as the parameter and return all the
recordings associated with that artist? I figure the SQL must find the
Artist name in tblArtists and then go via the linking table to return all
the recordings. I haven't had any success in the Query Builder yet.

Any ideas?

Regards,

Penny.

 

[Tom Wickerath]

Hi Penny,

Here is a parameter query that will work in Access:

SELECT tblArtists.ArtistName, tblRecordings.RecordingTitle
FROM tblRecordings
INNER JOIN (tblArtists INNER JOIN tblLINKArtistsAndRecordings
ON tblArtists.ArtistID = tblLINKArtistsAndRecordings.ArtistID)
ON tblRecordings.RecordingID = tblLINKArtistsAndRecordings.RecordingID
WHERE (((tblArtists.ArtistName) Like [Enter Artist Name] & "*"));

You will likely need to make some adjustments to make it work from a web page. This is not my
area of expertise. However, I suspect that you'll need to something similar to this:

Dim strSQL As String

strSQL = "SELECT ArtistName, RecordingTitle " _
& "FROM tblRecordings " _
& "INNER JOIN (tblArtists INNER JOIN tblLINKArtistsAndRecordings " _
& "ON tblArtists.ArtistID = tblLINKArtistsAndRecordings.ArtistID) " _
& "ON tblRecordings.RecordingID = tblLINKArtistsAndRecordings.RecordingID " _
& "WHERE (tblArtists.ArtistName) Like '" & txtKeyword & "'" & " & ""*"""


Note: As written, the txtKeyword value (ie. ArtistName) cannot include any single or double
quotes.

Tom
_________________________________


Hi All,

I've made a database for a record store and am currently attempting to make
it 'Web Searchable'. An Artist can be associated with many Recordings and
vice versa so there is a 'tblArtists' with an ArtistID, ArtistName and a few
other artist details, a 'tblRecordings' with a RecordingID, RecordingTitle
and a few other recording details and also a 'tblLINKArtistsAndRecordings'
with only the 'ArtistID' and 'RecordingID' composite primary key to link it
all together. Works well.

How do I create an SQL string that will take the name of an Artist(entered
as a 'keyword' by the web page user) as the parameter and return all the
recordings associated with that artist? I figure the SQL must find the
Artist name in tblArtists and then go via the linking table to return all
the recordings. I haven't had any success in the Query Builder yet.

Any ideas?

Regards,

Penny.

 

[Penny]

Thanks Tom,

I'll give your suggestion a whirl. The 'Many to Many' query thing is quite
hard to get my head around but with help from you guys I'm getting there.

Penny.

Hi Penny,

Here is a parameter query that will work in Access:

SELECT tblArtists.ArtistName, tblRecordings.RecordingTitle
FROM tblRecordings
INNER JOIN (tblArtists INNER JOIN tblLINKArtistsAndRecordings
ON tblArtists.ArtistID = tblLINKArtistsAndRecordings.ArtistID)
ON tblRecordings.RecordingID = tblLINKArtistsAndRecordings.RecordingID
WHERE (((tblArtists.ArtistName) Like [Enter Artist Name] & "*"));

You will likely need to make some adjustments to make it work from a web page. This is not my
area of expertise. However, I suspect that you'll need to something similar to this:

Dim strSQL As String

strSQL = "SELECT ArtistName, RecordingTitle " _
& "FROM tblRecordings " _
& "INNER JOIN (tblArtists INNER JOIN tblLINKArtistsAndRecordings " _
& "ON tblArtists.ArtistID = tblLINKArtistsAndRecordings.ArtistID) " _
& "ON tblRecordings.RecordingID =

tblLINKArtistsAndRecordings.RecordingID " _