If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Access Query Help - Select from 2 tables
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? |
#3
|
|||
|
|||
Access Query Help - Select from 2 tables
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? |
Thread Tools | |
Display Modes | |
|
|