Access Query Help - Select from 2 tables

I have a database with two tables I'm using for a movie collection. The DB
has tables "movies" and "actors". Table actors has columns ID and actor.
Table movies has columns ID,title,actors. I want to return the title based
on a search for an actor. The column actors (in the movies table) holds a
list of ID values (type of string), from the table "actors". Given a search
value of an actors name, how do you search the table actors for the name (to
get the ID value for the name) and then look thru the list in the column
actors (in the moves table) and return records from the movies table.

The primary key "ID" in both tables is an integer. The actors column in the
movies table is type of text, and contains a list, separated by a comma, of
numbers corresponding to the ID in actors - such as "1,14,23". Then the
table actors would contain info such as: 1-Harrison Ford, 14-Matt Damon, etc.

I'm using this in an HTA using Javascript and
ActiveXObject("ADODB.Connection") to make connections to the MS Access
database. Data provider = Microsoft.ACE.OLEDB.12.0 For Access 2007.

I'm thinking I need to query the actors table to find all the ID values that
match the search string. Then build another query with a bunch of "OR"
statements looking in the movies table, finding where column actors LIKE the
ID found in the first query. Then I would have to go thru these results and
make the actors column an array of values (using string.split(",") ) and
testing each of these values against the ID values returned from the first
result. This last step is needed for the following scenario: You have actors
with ID values such as 1,11,20. If you have a movie with actors 1,20. And
another movie with actors 11,20. And your search returns actor "1" in the
first query I mentioned above...the second query will return both movies
since it finds "1" and "11" using the "LIKE" in the second query.

But I thought maybe SQL might make this easier in one quick query?

Your structure is wrong in that you are using a string where you should have
multiple records.
You need 3 tables --
Movies - MovID, Title
Actors - ActID, Actor
MovAct - MovID, ActID

Create a one-to-many relationship between Movie and Actors setting
Referential Integerity and Cascade Update.

Use a form/subform for data entry with Master/Child links set with MovID. A
combo box in subform to select actor.

You will need to parse your current Movie data to fill the MovAct table.
First backup your database. Alternately use and append query and update
query.
Append Left([Movie].[Actor], InStr([Movie].[Actor], ",")-1) and then update
[Movie].[Actor] with Mid([Movie].[Actor], InStr([Movie].[Actor], ",")+1) to
remove what was appended. Then when only one actor is left in the field
just append.

"greymole" wrote:

I have a database with two tables I'm using for a movie collection. The DB
has tables "movies" and "actors". Table actors has columns ID and actor.
Table movies has columns ID,title,actors. I want to return the title based
on a search for an actor. The column actors (in the movies table) holds a
list of ID values (type of string), from the table "actors". Given a search
value of an actors name, how do you search the table actors for the name (to
get the ID value for the name) and then look thru the list in the column
actors (in the moves table) and return records from the movies table.

The primary key "ID" in both tables is an integer. The actors column in the
movies table is type of text, and contains a list, separated by a comma, of
numbers corresponding to the ID in actors - such as "1,14,23". Then the
table actors would contain info such as: 1-Harrison Ford, 14-Matt Damon, etc.

I'm using this in an HTA using Javascript and
ActiveXObject("ADODB.Connection") to make connections to the MS Access
database. Data provider = Microsoft.ACE.OLEDB.12.0 For Access 2007.

I'm thinking I need to query the actors table to find all the ID values that
match the search string. Then build another query with a bunch of "OR"
statements looking in the movies table, finding where column actors LIKE the
ID found in the first query. Then I would have to go thru these results and
make the actors column an array of values (using string.split(",") ) and
testing each of these values against the ID values returned from the first
result. This last step is needed for the following scenario: You have actors
with ID values such as 1,11,20. If you have a movie with actors 1,20. And
another movie with actors 11,20. And your search returns actor "1" in the
first query I mentioned above...the second query will return both movies
since it finds "1" and "11" using the "LIKE" in the second query.

But I thought maybe SQL might make this easier in one quick query?

Thanks for the ideas. I've started moving my database information to a new
table which oles the MovID and ActID. I'm not exactly sure what the append
and update you suggested do, and how to do it. I'm a novice with access, but
have a good handle on using the "select" to find records using the activeX.
If I get the tables set p correctly like you suggested, what would the select
statement be, given a search string for the movie title AND/OR search string
for actor name?

"KARL DEWEY" wrote:

Your structure is wrong in that you are using a string where you should have
multiple records.
You need 3 tables --
Movies - MovID, Title
Actors - ActID, Actor
MovAct - MovID, ActID

Create a one-to-many relationship between Movie and Actors setting
Referential Integerity and Cascade Update.

Use a form/subform for data entry with Master/Child links set with MovID. A
combo box in subform to select actor.

You will need to parse your current Movie data to fill the MovAct table.
First backup your database. Alternately use and append query and update
query.
Append Left([Movie].[Actor], InStr([Movie].[Actor], ",")-1) and then update
[Movie].[Actor] with Mid([Movie].[Actor], InStr([Movie].[Actor], ",")+1) to
remove what was appended. Then when only one actor is left in the field
just append.

"greymole" wrote:

I have a database with two tables I'm using for a movie collection. The DB
has tables "movies" and "actors". Table actors has columns ID and actor.
Table movies has columns ID,title,actors. I want to return the title based
on a search for an actor. The column actors (in the movies table) holds a
list of ID values (type of string), from the table "actors". Given a search
value of an actors name, how do you search the table actors for the name (to
get the ID value for the name) and then look thru the list in the column
actors (in the moves table) and return records from the movies table.

The primary key "ID" in both tables is an integer. The actors column in the
movies table is type of text, and contains a list, separated by a comma, of
numbers corresponding to the ID in actors - such as "1,14,23". Then the
table actors would contain info such as: 1-Harrison Ford, 14-Matt Damon, etc.

I'm using this in an HTA using Javascript and
ActiveXObject("ADODB.Connection") to make connections to the MS Access
database. Data provider = Microsoft.ACE.OLEDB.12.0 For Access 2007.

I'm thinking I need to query the actors table to find all the ID values that
match the search string. Then build another query with a bunch of "OR"
statements looking in the movies table, finding where column actors LIKE the
ID found in the first query. Then I would have to go thru these results and
make the actors column an array of values (using string.split(",") ) and
testing each of these values against the ID values returned from the first
result. This last step is needed for the following scenario: You have actors
with ID values such as 1,11,20. If you have a movie with actors 1,20. And
another movie with actors 11,20. And your search returns actor "1" in the
first query I mentioned above...the second query will return both movies
since it finds "1" and "11" using the "LIKE" in the second query.

But I thought maybe SQL might make this easier in one quick query?