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 |
#11
|
|||
|
|||
On Mon, 26 Sep 2005 06:59:31 -0500, "Ed Warren"
wrote: Nope. You said code, and it went out the window. Got so many different codes floating around, Yes I have VB 6. But not a clue how to use it. Got the books, and made a few stabs. But they didn't do what I wanted. What you are asking is: Given a data structure where I can have as many actors as I want (not limited to 12), how do I convert this into a 'view' that has up to 12 actors in a row, so I can create a report to use to print the DVD labels. Here is a way (not necessarly the quickest, neatest, or best), but direct and reasonably simple. First you need yet another field in your 'Role' table let's call it 'Order' So in the case below Humphery Bogart (101) would be Order (1) in Movie Casablanca (101) Also you might might want to handle the case where the same actor plays several characters in a movie: e.g. Halley Mills in parent trap. (another table? 1:M) Now you can build a set of queries that will return up to the top 12 actors for each movie and assign each to a new 'field' in your query. Examples: GetActorsNames SELECT Roles.MovieID, [FirstName] & ", " & [LastName] & ": " & [Character] AS ActorNameCharacter, Roles.Order FROM Roles INNER JOIN Actors ON Roles.ActorID = Actors.ActorID WHERE (((Roles.Order)=12)) ORDER BY Roles.MovieID, Roles.Order; This returns: GetActorNames MovieID ActorNameCharacter Order 101 Humphrey, Bogart: Rick 1 101 Ingrid, Bergman: Ilsa 2 102 Ingrid, Bergman: Character1 1 102 Humphrey, Bogart: Character2 2 A Visual Basic Module to get the Actors for a movie given the movieID and order (note: this is just one of many, many ways to do this, you could also work with a query in code and return just one string with all the actors with an order =12 in it) Public Function GetActor(passedMovieID As Long, passedOrder As Long) As String Dim ActorID As String Dim lookupString As String lookupString = "[Order]=" & passedOrder & " and [MovieID] = " & passedMovieID ActorID = Nz(DLookup("ActorNameCharacter", "GetActorNames", lookupString), "") GetActor = ActorID End Function A query to get the actors:roles for a movie: SELECT DISTINCT Roles.MovieID, getActor([Movieid],1) AS Actor1, getActor([Movieid],2) AS Actor2, getActor([Movieid],3) AS Actor3, getActor([Movieid],4) AS Actor4 FROM Roles; GetActorRoles MovieID Actor1 Actor2 Actor3 Actor4 101 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa 102 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Finally put it all together with a query to get the Movie specific stuff GetMovieStuff SELECT Movies.Title, Movies.Director, Movies.PlayingTime, GetActorRoles.Actor1, GetActorRoles.Actor2, GetActorRoles.Actor3, GetActorRoles.Actor4 FROM Movies INNER JOIN GetActorRoles ON Movies.MovieID = GetActorRoles.MovieID; GetMovieStuff Title Director PlayingTime Actor1 Actor2 Actor3 Actor4 Casablance someone 90 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa The Maltese Falcon someoneelse 100 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Hope this helps more than it confuses the issue. Ed Warren. "Husky" wrote in message .. . On Mon, 26 Sep 2005 00:41:30 -0400, "Randy Harris" wrote: Suggest you read the replies from Ed and John more carefully. You need 3 tables! You don't really need to add the character names (you could if you wanted), but think of the "junction" table, as John called it, as Roles. So you have tables: Actor Movie Role Actor will contain the names (and other details) of every actor in all the movies. Movie will contain the names (and other details you wish) of the movies. Role will contain one record for each actor appearing in a move: Example: Actor ActorID LastName FirstName DOB ------- ----------- ---------- ----- 101 Bogart Humphrey 102 Bergman Ingrid Movie MovieID Title Director PlayingTime ------- ---------- -------- ------------ 101 Casablanca 102 The Maltese Falcon Role MovieID ActorID Character -------- --------- --------- 101 101 Rick 101 102 Ilsa 102 101 What you'll likely do, is have one form to add/edit Actors. Another for Movies. And a third, where you'll select the Actor and the Movie from lists, for Roles. You will have no limits to the number of Movies, Actors or Roles. There are a great many compelling reasons for doing it this way. You will have lots of problems attempting to do it the way you are currently. This is how a relational database management system works. Hope this helps to clarify things, It's making a bit more sense. But I'm using the form as the template to print to the DVD labels. Killing 2 with one stone. It updates my video table. How can I keep the current form's design, with all the different tables and tie it all together ? -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#12
|
|||
|
|||
You write code in the Access Database using VBA code.
Open your MS Access Database Open a new module Copy the code below into the module close and save it write your queries Maybe someone else can help with a query that will do what you want, but I do it with code. Ed Warren "Husky" wrote in message ... On Mon, 26 Sep 2005 06:59:31 -0500, "Ed Warren" wrote: Nope. You said code, and it went out the window. Got so many different codes floating around, Yes I have VB 6. But not a clue how to use it. Got the books, and made a few stabs. But they didn't do what I wanted. What you are asking is: Given a data structure where I can have as many actors as I want (not limited to 12), how do I convert this into a 'view' that has up to 12 actors in a row, so I can create a report to use to print the DVD labels. Here is a way (not necessarly the quickest, neatest, or best), but direct and reasonably simple. First you need yet another field in your 'Role' table let's call it 'Order' So in the case below Humphery Bogart (101) would be Order (1) in Movie Casablanca (101) Also you might might want to handle the case where the same actor plays several characters in a movie: e.g. Halley Mills in parent trap. (another table? 1:M) Now you can build a set of queries that will return up to the top 12 actors for each movie and assign each to a new 'field' in your query. Examples: GetActorsNames SELECT Roles.MovieID, [FirstName] & ", " & [LastName] & ": " & [Character] AS ActorNameCharacter, Roles.Order FROM Roles INNER JOIN Actors ON Roles.ActorID = Actors.ActorID WHERE (((Roles.Order)=12)) ORDER BY Roles.MovieID, Roles.Order; This returns: GetActorNames MovieID ActorNameCharacter Order 101 Humphrey, Bogart: Rick 1 101 Ingrid, Bergman: Ilsa 2 102 Ingrid, Bergman: Character1 1 102 Humphrey, Bogart: Character2 2 A Visual Basic Module to get the Actors for a movie given the movieID and order (note: this is just one of many, many ways to do this, you could also work with a query in code and return just one string with all the actors with an order =12 in it) Public Function GetActor(passedMovieID As Long, passedOrder As Long) As String Dim ActorID As String Dim lookupString As String lookupString = "[Order]=" & passedOrder & " and [MovieID] = " & passedMovieID ActorID = Nz(DLookup("ActorNameCharacter", "GetActorNames", lookupString), "") GetActor = ActorID End Function A query to get the actors:roles for a movie: SELECT DISTINCT Roles.MovieID, getActor([Movieid],1) AS Actor1, getActor([Movieid],2) AS Actor2, getActor([Movieid],3) AS Actor3, getActor([Movieid],4) AS Actor4 FROM Roles; GetActorRoles MovieID Actor1 Actor2 Actor3 Actor4 101 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa 102 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Finally put it all together with a query to get the Movie specific stuff GetMovieStuff SELECT Movies.Title, Movies.Director, Movies.PlayingTime, GetActorRoles.Actor1, GetActorRoles.Actor2, GetActorRoles.Actor3, GetActorRoles.Actor4 FROM Movies INNER JOIN GetActorRoles ON Movies.MovieID = GetActorRoles.MovieID; GetMovieStuff Title Director PlayingTime Actor1 Actor2 Actor3 Actor4 Casablance someone 90 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa The Maltese Falcon someoneelse 100 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Hope this helps more than it confuses the issue. Ed Warren. "Husky" wrote in message . .. On Mon, 26 Sep 2005 00:41:30 -0400, "Randy Harris" wrote: Suggest you read the replies from Ed and John more carefully. You need 3 tables! You don't really need to add the character names (you could if you wanted), but think of the "junction" table, as John called it, as Roles. So you have tables: Actor Movie Role Actor will contain the names (and other details) of every actor in all the movies. Movie will contain the names (and other details you wish) of the movies. Role will contain one record for each actor appearing in a move: Example: Actor ActorID LastName FirstName DOB ------- ----------- ---------- ----- 101 Bogart Humphrey 102 Bergman Ingrid Movie MovieID Title Director PlayingTime ------- ---------- -------- ------------ 101 Casablanca 102 The Maltese Falcon Role MovieID ActorID Character -------- --------- --------- 101 101 Rick 101 102 Ilsa 102 101 What you'll likely do, is have one form to add/edit Actors. Another for Movies. And a third, where you'll select the Actor and the Movie from lists, for Roles. You will have no limits to the number of Movies, Actors or Roles. There are a great many compelling reasons for doing it this way. You will have lots of problems attempting to do it the way you are currently. This is how a relational database management system works. Hope this helps to clarify things, It's making a bit more sense. But I'm using the form as the template to print to the DVD labels. Killing 2 with one stone. It updates my video table. How can I keep the current form's design, with all the different tables and tie it all together ? -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#13
|
|||
|
|||
Husky, I am not sure if I understand your question but the following code
will find any part of a string in any field in a table. It puts the ID's of each record in a table of the results. To view the result you will need to query your main table based on the ID's of the results table. In the example below this is displayed in a form called: Results (which you will need to create) Function Searcher(box1) Dim dbs As Database, rst As Recordset, strsql As String Set dbs = CurrentDb match = "OR" dbs.Execute " Delete SearchResultsPerson.* FROM SearchResultsPerson;" dbs.Execute "INSERT INTO SearchResultsPerson ( ID ) " _ & "SELECT DNRS.ID FROM DNRS " _ & " WHERE [salutation] Like '*" & [box1] & "*' " _ & " " & [match] & " [Surname] Like '*" & [box1] & "*' " _ & " " & [match] & " [Initial] Like '*" & [box1] & "*' " _ & " " & [match] & " [Address1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [town] Like '*" & [box1] & "*' " _ & " " & [match] & " [State] Like '*" & [box1] & "*' " _ & " " & [match] & " [Postcode] Like '*" & [box1] & "*' " _ & " " & [match] & " [Notes] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone2] Like '*" & [box1] & "*' " _ & " " & [match] & " [Fax] Like '*" & [box1] & "*' " _ & " " & [match] & " [mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' ;" Searcher is the name of the function so you call it from the following code Private Sub Command43_Click() Dim SearchString SearchString = InputBox("Please enter the text that you would like to find .....", "Find ...") If IsNull(SearchString) Then Else If SearchString = "" Then Else Call Searcher(SearchString) DoCmd.OpenForm ("Results") End If End If End Sub Important - for this to work you will need to declare box1 as a public variable Public box1, match (place this at the begining of a module ) The code that calls the function resides in a Form (in my example it is called from the click of command button 43) If you type in rob - then it will find any combination of this word like in Robert or Robin or Wroby "Husky" wrote: You can't get there from here. Or so it seems. I got my DVD catalog and form working fine for display on the Jewel cases. square box on one side, DVD round image cover on other. The form isn't really the problem. For future reference I would like to search to find all the DVD's that actorx appears in. But to be able to enter all the different actors and titles on the DVD [think it's 4-6 hours of play] I had to go with the max number of actors [12] and create fields actor, actor1 etc.. And also did the same with titles, title, title1, etc.. But to find all the DVD's' that actor x appeared in with a query, I needed to enter the full name, [partial failed] and that only worked if I entered the query in actorx field that actorx actually appears in. ie: actor x might be in actor1 field, but if I query on field actor, nothing pops up, even though actorx is on that DVD's. Just may appear in actor 1, actor2, etc-12 fields. with 12 identical fields for actor, on each record, how can I search for all the DVD's' actor x is on ? I tried the wild card, but obviously don't understand it. -- more pix @ http://members.toast.net/cbminfo/index.html |
#14
|
|||
|
|||
On Mon, 26 Sep 2005 09:31:28 -0500, "Ed Warren"
wrote: You write code in the Access Database using VBA code. ok, I'll try it. Open your MS Access Database Open a new module Copy the code below into the module close and save it write your queries Maybe someone else can help with a query that will do what you want, but I do it with code. Ed Warren "Husky" wrote in message .. . On Mon, 26 Sep 2005 06:59:31 -0500, "Ed Warren" wrote: Nope. You said code, and it went out the window. Got so many different codes floating around, Yes I have VB 6. But not a clue how to use it. Got the books, and made a few stabs. But they didn't do what I wanted. What you are asking is: Given a data structure where I can have as many actors as I want (not limited to 12), how do I convert this into a 'view' that has up to 12 actors in a row, so I can create a report to use to print the DVD labels. Here is a way (not necessarly the quickest, neatest, or best), but direct and reasonably simple. First you need yet another field in your 'Role' table let's call it 'Order' So in the case below Humphery Bogart (101) would be Order (1) in Movie Casablanca (101) Also you might might want to handle the case where the same actor plays several characters in a movie: e.g. Halley Mills in parent trap. (another table? 1:M) Now you can build a set of queries that will return up to the top 12 actors for each movie and assign each to a new 'field' in your query. Examples: GetActorsNames SELECT Roles.MovieID, [FirstName] & ", " & [LastName] & ": " & [Character] AS ActorNameCharacter, Roles.Order FROM Roles INNER JOIN Actors ON Roles.ActorID = Actors.ActorID WHERE (((Roles.Order)=12)) ORDER BY Roles.MovieID, Roles.Order; This returns: GetActorNames MovieID ActorNameCharacter Order 101 Humphrey, Bogart: Rick 1 101 Ingrid, Bergman: Ilsa 2 102 Ingrid, Bergman: Character1 1 102 Humphrey, Bogart: Character2 2 A Visual Basic Module to get the Actors for a movie given the movieID and order (note: this is just one of many, many ways to do this, you could also work with a query in code and return just one string with all the actors with an order =12 in it) Public Function GetActor(passedMovieID As Long, passedOrder As Long) As String Dim ActorID As String Dim lookupString As String lookupString = "[Order]=" & passedOrder & " and [MovieID] = " & passedMovieID ActorID = Nz(DLookup("ActorNameCharacter", "GetActorNames", lookupString), "") GetActor = ActorID End Function A query to get the actors:roles for a movie: SELECT DISTINCT Roles.MovieID, getActor([Movieid],1) AS Actor1, getActor([Movieid],2) AS Actor2, getActor([Movieid],3) AS Actor3, getActor([Movieid],4) AS Actor4 FROM Roles; GetActorRoles MovieID Actor1 Actor2 Actor3 Actor4 101 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa 102 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Finally put it all together with a query to get the Movie specific stuff GetMovieStuff SELECT Movies.Title, Movies.Director, Movies.PlayingTime, GetActorRoles.Actor1, GetActorRoles.Actor2, GetActorRoles.Actor3, GetActorRoles.Actor4 FROM Movies INNER JOIN GetActorRoles ON Movies.MovieID = GetActorRoles.MovieID; GetMovieStuff Title Director PlayingTime Actor1 Actor2 Actor3 Actor4 Casablance someone 90 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa The Maltese Falcon someoneelse 100 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Hope this helps more than it confuses the issue. Ed Warren. "Husky" wrote in message ... On Mon, 26 Sep 2005 00:41:30 -0400, "Randy Harris" wrote: Suggest you read the replies from Ed and John more carefully. You need 3 tables! You don't really need to add the character names (you could if you wanted), but think of the "junction" table, as John called it, as Roles. So you have tables: Actor Movie Role Actor will contain the names (and other details) of every actor in all the movies. Movie will contain the names (and other details you wish) of the movies. Role will contain one record for each actor appearing in a move: Example: Actor ActorID LastName FirstName DOB ------- ----------- ---------- ----- 101 Bogart Humphrey 102 Bergman Ingrid Movie MovieID Title Director PlayingTime ------- ---------- -------- ------------ 101 Casablanca 102 The Maltese Falcon Role MovieID ActorID Character -------- --------- --------- 101 101 Rick 101 102 Ilsa 102 101 What you'll likely do, is have one form to add/edit Actors. Another for Movies. And a third, where you'll select the Actor and the Movie from lists, for Roles. You will have no limits to the number of Movies, Actors or Roles. There are a great many compelling reasons for doing it this way. You will have lots of problems attempting to do it the way you are currently. This is how a relational database management system works. Hope this helps to clarify things, It's making a bit more sense. But I'm using the form as the template to print to the DVD labels. Killing 2 with one stone. It updates my video table. How can I keep the current form's design, with all the different tables and tie it all together ? -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#15
|
|||
|
|||
On Mon, 26 Sep 2005 07:51:01 -0700, "downunderling"
wrote: Husky, I am not sure if I understand your question but the following code will find any part of a string in any field in a table. It puts the ID's of each record in a table of the results. To view the result you will need to query your main table based on the ID's of the results table. In the example below this is displayed in a form called: Results (which you will need to create) I can find what I'm looking for 1 by 1, in the form with partials by using the binoculars on the toolbar. But for a printout I'll eventually need to select specific DVD's and only some fields. Obviously won't need the image for a printout. Just for the labels. I want to be able to sort and display the entire video DB by select criteria. But in the form mode, and binoculars, I only get 1 record at a time. ie: Stallone on 3 different DVD's, I want all 3 DVD's selected to print on one 8.5x11 page. Something like a report. Function Searcher(box1) Dim dbs As Database, rst As Recordset, strsql As String Set dbs = CurrentDb match = "OR" dbs.Execute " Delete SearchResultsPerson.* FROM SearchResultsPerson;" dbs.Execute "INSERT INTO SearchResultsPerson ( ID ) " _ & "SELECT DNRS.ID FROM DNRS " _ & " WHERE [salutation] Like '*" & [box1] & "*' " _ & " " & [match] & " [Surname] Like '*" & [box1] & "*' " _ & " " & [match] & " [Initial] Like '*" & [box1] & "*' " _ & " " & [match] & " [Address1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [town] Like '*" & [box1] & "*' " _ & " " & [match] & " [State] Like '*" & [box1] & "*' " _ & " " & [match] & " [Postcode] Like '*" & [box1] & "*' " _ & " " & [match] & " [Notes] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone2] Like '*" & [box1] & "*' " _ & " " & [match] & " [Fax] Like '*" & [box1] & "*' " _ & " " & [match] & " [mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' ;" Searcher is the name of the function so you call it from the following code Private Sub Command43_Click() Dim SearchString SearchString = InputBox("Please enter the text that you would like to find ....", "Find ...") If IsNull(SearchString) Then Else If SearchString = "" Then Else Call Searcher(SearchString) DoCmd.OpenForm ("Results") End If End If End Sub Important - for this to work you will need to declare box1 as a public variable Public box1, match (place this at the begining of a module ) The code that calls the function resides in a Form (in my example it is called from the click of command button 43) If you type in rob - then it will find any combination of this word like in Robert or Robin or Wroby "Husky" wrote: You can't get there from here. Or so it seems. I got my DVD catalog and form working fine for display on the Jewel cases. square box on one side, DVD round image cover on other. The form isn't really the problem. For future reference I would like to search to find all the DVD's that actorx appears in. But to be able to enter all the different actors and titles on the DVD [think it's 4-6 hours of play] I had to go with the max number of actors [12] and create fields actor, actor1 etc.. And also did the same with titles, title, title1, etc.. But to find all the DVD's' that actor x appeared in with a query, I needed to enter the full name, [partial failed] and that only worked if I entered the query in actorx field that actorx actually appears in. ie: actor x might be in actor1 field, but if I query on field actor, nothing pops up, even though actorx is on that DVD's. Just may appear in actor 1, actor2, etc-12 fields. with 12 identical fields for actor, on each record, how can I search for all the DVD's' actor x is on ? I tried the wild card, but obviously don't understand it. -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#16
|
|||
|
|||
Here is a cross-tab query that bypasses the need for the 'code'
TRANSFORM First(GetActorNames.ActorNameCharacter) AS FirstOfActorNameCharacter SELECT GetActorNames.MovieID FROM GetActorNames GROUP BY GetActorNames.MovieID PIVOT GetActorNames.Order; Ed Warren "Husky" wrote in message ... On Mon, 26 Sep 2005 09:31:28 -0500, "Ed Warren" wrote: You write code in the Access Database using VBA code. ok, I'll try it. Open your MS Access Database Open a new module Copy the code below into the module close and save it write your queries Maybe someone else can help with a query that will do what you want, but I do it with code. Ed Warren "Husky" wrote in message . .. On Mon, 26 Sep 2005 06:59:31 -0500, "Ed Warren" wrote: Nope. You said code, and it went out the window. Got so many different codes floating around, Yes I have VB 6. But not a clue how to use it. Got the books, and made a few stabs. But they didn't do what I wanted. What you are asking is: Given a data structure where I can have as many actors as I want (not limited to 12), how do I convert this into a 'view' that has up to 12 actors in a row, so I can create a report to use to print the DVD labels. Here is a way (not necessarly the quickest, neatest, or best), but direct and reasonably simple. First you need yet another field in your 'Role' table let's call it 'Order' So in the case below Humphery Bogart (101) would be Order (1) in Movie Casablanca (101) Also you might might want to handle the case where the same actor plays several characters in a movie: e.g. Halley Mills in parent trap. (another table? 1:M) Now you can build a set of queries that will return up to the top 12 actors for each movie and assign each to a new 'field' in your query. Examples: GetActorsNames SELECT Roles.MovieID, [FirstName] & ", " & [LastName] & ": " & [Character] AS ActorNameCharacter, Roles.Order FROM Roles INNER JOIN Actors ON Roles.ActorID = Actors.ActorID WHERE (((Roles.Order)=12)) ORDER BY Roles.MovieID, Roles.Order; This returns: GetActorNames MovieID ActorNameCharacter Order 101 Humphrey, Bogart: Rick 1 101 Ingrid, Bergman: Ilsa 2 102 Ingrid, Bergman: Character1 1 102 Humphrey, Bogart: Character2 2 A Visual Basic Module to get the Actors for a movie given the movieID and order (note: this is just one of many, many ways to do this, you could also work with a query in code and return just one string with all the actors with an order =12 in it) Public Function GetActor(passedMovieID As Long, passedOrder As Long) As String Dim ActorID As String Dim lookupString As String lookupString = "[Order]=" & passedOrder & " and [MovieID] = " & passedMovieID ActorID = Nz(DLookup("ActorNameCharacter", "GetActorNames", lookupString), "") GetActor = ActorID End Function A query to get the actors:roles for a movie: SELECT DISTINCT Roles.MovieID, getActor([Movieid],1) AS Actor1, getActor([Movieid],2) AS Actor2, getActor([Movieid],3) AS Actor3, getActor([Movieid],4) AS Actor4 FROM Roles; GetActorRoles MovieID Actor1 Actor2 Actor3 Actor4 101 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa 102 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Finally put it all together with a query to get the Movie specific stuff GetMovieStuff SELECT Movies.Title, Movies.Director, Movies.PlayingTime, GetActorRoles.Actor1, GetActorRoles.Actor2, GetActorRoles.Actor3, GetActorRoles.Actor4 FROM Movies INNER JOIN GetActorRoles ON Movies.MovieID = GetActorRoles.MovieID; GetMovieStuff Title Director PlayingTime Actor1 Actor2 Actor3 Actor4 Casablance someone 90 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa The Maltese Falcon someoneelse 100 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Hope this helps more than it confuses the issue. Ed Warren. "Husky" wrote in message m... On Mon, 26 Sep 2005 00:41:30 -0400, "Randy Harris" wrote: Suggest you read the replies from Ed and John more carefully. You need 3 tables! You don't really need to add the character names (you could if you wanted), but think of the "junction" table, as John called it, as Roles. So you have tables: Actor Movie Role Actor will contain the names (and other details) of every actor in all the movies. Movie will contain the names (and other details you wish) of the movies. Role will contain one record for each actor appearing in a move: Example: Actor ActorID LastName FirstName DOB ------- ----------- ---------- ----- 101 Bogart Humphrey 102 Bergman Ingrid Movie MovieID Title Director PlayingTime ------- ---------- -------- ------------ 101 Casablanca 102 The Maltese Falcon Role MovieID ActorID Character -------- --------- --------- 101 101 Rick 101 102 Ilsa 102 101 What you'll likely do, is have one form to add/edit Actors. Another for Movies. And a third, where you'll select the Actor and the Movie from lists, for Roles. You will have no limits to the number of Movies, Actors or Roles. There are a great many compelling reasons for doing it this way. You will have lots of problems attempting to do it the way you are currently. This is how a relational database management system works. Hope this helps to clarify things, It's making a bit more sense. But I'm using the form as the template to print to the DVD labels. Killing 2 with one stone. It updates my video table. How can I keep the current form's design, with all the different tables and tie it all together ? -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#17
|
|||
|
|||
On Mon, 26 Sep 2005 13:40:02 -0500, "Ed Warren"
wrote: Here is a cross-tab query that bypasses the need for the 'code' TRANSFORM First(GetActorNames.ActorNameCharacter) AS FirstOfActorNameCharacter SELECT GetActorNames.MovieID FROM GetActorNames GROUP BY GetActorNames.MovieID PIVOT GetActorNames.Order; Keeping it. Thanks. Ed Warren "Husky" wrote in message .. . On Mon, 26 Sep 2005 09:31:28 -0500, "Ed Warren" wrote: You write code in the Access Database using VBA code. ok, I'll try it. Open your MS Access Database Open a new module Copy the code below into the module close and save it write your queries Maybe someone else can help with a query that will do what you want, but I do it with code. Ed Warren "Husky" wrote in message ... On Mon, 26 Sep 2005 06:59:31 -0500, "Ed Warren" wrote: Nope. You said code, and it went out the window. Got so many different codes floating around, Yes I have VB 6. But not a clue how to use it. Got the books, and made a few stabs. But they didn't do what I wanted. What you are asking is: Given a data structure where I can have as many actors as I want (not limited to 12), how do I convert this into a 'view' that has up to 12 actors in a row, so I can create a report to use to print the DVD labels. Here is a way (not necessarly the quickest, neatest, or best), but direct and reasonably simple. First you need yet another field in your 'Role' table let's call it 'Order' So in the case below Humphery Bogart (101) would be Order (1) in Movie Casablanca (101) Also you might might want to handle the case where the same actor plays several characters in a movie: e.g. Halley Mills in parent trap. (another table? 1:M) Now you can build a set of queries that will return up to the top 12 actors for each movie and assign each to a new 'field' in your query. Examples: GetActorsNames SELECT Roles.MovieID, [FirstName] & ", " & [LastName] & ": " & [Character] AS ActorNameCharacter, Roles.Order FROM Roles INNER JOIN Actors ON Roles.ActorID = Actors.ActorID WHERE (((Roles.Order)=12)) ORDER BY Roles.MovieID, Roles.Order; This returns: GetActorNames MovieID ActorNameCharacter Order 101 Humphrey, Bogart: Rick 1 101 Ingrid, Bergman: Ilsa 2 102 Ingrid, Bergman: Character1 1 102 Humphrey, Bogart: Character2 2 A Visual Basic Module to get the Actors for a movie given the movieID and order (note: this is just one of many, many ways to do this, you could also work with a query in code and return just one string with all the actors with an order =12 in it) Public Function GetActor(passedMovieID As Long, passedOrder As Long) As String Dim ActorID As String Dim lookupString As String lookupString = "[Order]=" & passedOrder & " and [MovieID] = " & passedMovieID ActorID = Nz(DLookup("ActorNameCharacter", "GetActorNames", lookupString), "") GetActor = ActorID End Function A query to get the actors:roles for a movie: SELECT DISTINCT Roles.MovieID, getActor([Movieid],1) AS Actor1, getActor([Movieid],2) AS Actor2, getActor([Movieid],3) AS Actor3, getActor([Movieid],4) AS Actor4 FROM Roles; GetActorRoles MovieID Actor1 Actor2 Actor3 Actor4 101 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa 102 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Finally put it all together with a query to get the Movie specific stuff GetMovieStuff SELECT Movies.Title, Movies.Director, Movies.PlayingTime, GetActorRoles.Actor1, GetActorRoles.Actor2, GetActorRoles.Actor3, GetActorRoles.Actor4 FROM Movies INNER JOIN GetActorRoles ON Movies.MovieID = GetActorRoles.MovieID; GetMovieStuff Title Director PlayingTime Actor1 Actor2 Actor3 Actor4 Casablance someone 90 Humphrey, Bogart: Rick Ingrid, Bergman: Ilsa The Maltese Falcon someoneelse 100 Ingrid, Bergman: Character1 Humphrey, Bogart: Character2 Hope this helps more than it confuses the issue. Ed Warren. "Husky" wrote in message om... On Mon, 26 Sep 2005 00:41:30 -0400, "Randy Harris" wrote: Suggest you read the replies from Ed and John more carefully. You need 3 tables! You don't really need to add the character names (you could if you wanted), but think of the "junction" table, as John called it, as Roles. So you have tables: Actor Movie Role Actor will contain the names (and other details) of every actor in all the movies. Movie will contain the names (and other details you wish) of the movies. Role will contain one record for each actor appearing in a move: Example: Actor ActorID LastName FirstName DOB ------- ----------- ---------- ----- 101 Bogart Humphrey 102 Bergman Ingrid Movie MovieID Title Director PlayingTime ------- ---------- -------- ------------ 101 Casablanca 102 The Maltese Falcon Role MovieID ActorID Character -------- --------- --------- 101 101 Rick 101 102 Ilsa 102 101 What you'll likely do, is have one form to add/edit Actors. Another for Movies. And a third, where you'll select the Actor and the Movie from lists, for Roles. You will have no limits to the number of Movies, Actors or Roles. There are a great many compelling reasons for doing it this way. You will have lots of problems attempting to do it the way you are currently. This is how a relational database management system works. Hope this helps to clarify things, It's making a bit more sense. But I'm using the form as the template to print to the DVD labels. Killing 2 with one stone. It updates my video table. How can I keep the current form's design, with all the different tables and tie it all together ? -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#18
|
|||
|
|||
This code will do what you want.
You need to create a table named: SearchResultsPerson with a single Field in it called ID (format as Integer) In your table of Video data add also add a field called ID (format as autonumber) Change the names (Surname, Initial, etc ) in this example of code to the names of your fields: actor, actor1, actor2, title, title1 etc) add additional lines if necessary. 1 line per field in the table. The last line of this list must have a ; at the end of it all the other must have an undescore _ Rename the name of your table to DNRS when you run the code, you can then look at the SearchResultsPerson which will now contain the ID of each record with a match. "Husky" wrote: On Mon, 26 Sep 2005 07:51:01 -0700, "downunderling" wrote: Husky, I am not sure if I understand your question but the following code will find any part of a string in any field in a table. It puts the ID's of each record in a table of the results. To view the result you will need to query your main table based on the ID's of the results table. In the example below this is displayed in a form called: Results (which you will need to create) I can find what I'm looking for 1 by 1, in the form with partials by using the binoculars on the toolbar. But for a printout I'll eventually need to select specific DVD's and only some fields. Obviously won't need the image for a printout. Just for the labels. I want to be able to sort and display the entire video DB by select criteria. But in the form mode, and binoculars, I only get 1 record at a time. ie: Stallone on 3 different DVD's, I want all 3 DVD's selected to print on one 8.5x11 page. Something like a report. Function Searcher(box1) Dim dbs As Database, rst As Recordset, strsql As String Set dbs = CurrentDb match = "OR" dbs.Execute " Delete SearchResultsPerson.* FROM SearchResultsPerson;" dbs.Execute "INSERT INTO SearchResultsPerson ( ID ) " _ & "SELECT DNRS.ID FROM DNRS " _ & " WHERE [salutation] Like '*" & [box1] & "*' " _ & " " & [match] & " [Surname] Like '*" & [box1] & "*' " _ & " " & [match] & " [Initial] Like '*" & [box1] & "*' " _ & " " & [match] & " [Address1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [town] Like '*" & [box1] & "*' " _ & " " & [match] & " [State] Like '*" & [box1] & "*' " _ & " " & [match] & " [Postcode] Like '*" & [box1] & "*' " _ & " " & [match] & " [Notes] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone2] Like '*" & [box1] & "*' " _ & " " & [match] & " [Fax] Like '*" & [box1] & "*' " _ & " " & [match] & " [mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' ;" Searcher is the name of the function so you call it from the following code Private Sub Command43_Click() Dim SearchString SearchString = InputBox("Please enter the text that you would like to find ....", "Find ...") If IsNull(SearchString) Then Else If SearchString = "" Then Else Call Searcher(SearchString) DoCmd.OpenForm ("Results") End If End If End Sub Important - for this to work you will need to declare box1 as a public variable Public box1, match (place this at the begining of a module ) The code that calls the function resides in a Form (in my example it is called from the click of command button 43) If you type in rob - then it will find any combination of this word like in Robert or Robin or Wroby "Husky" wrote: You can't get there from here. Or so it seems. I got my DVD catalog and form working fine for display on the Jewel cases. square box on one side, DVD round image cover on other. The form isn't really the problem. For future reference I would like to search to find all the DVD's that actorx appears in. But to be able to enter all the different actors and titles on the DVD [think it's 4-6 hours of play] I had to go with the max number of actors [12] and create fields actor, actor1 etc.. And also did the same with titles, title, title1, etc.. But to find all the DVD's' that actor x appeared in with a query, I needed to enter the full name, [partial failed] and that only worked if I entered the query in actorx field that actorx actually appears in. ie: actor x might be in actor1 field, but if I query on field actor, nothing pops up, even though actorx is on that DVD's. Just may appear in actor 1, actor2, etc-12 fields. with 12 identical fields for actor, on each record, how can I search for all the DVD's' actor x is on ? I tried the wild card, but obviously don't understand it. -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#19
|
|||
|
|||
On Mon, 26 Sep 2005 16:05:02 -0700, "downunderling"
wrote: This code will do what you want. You need to create a table named: SearchResultsPerson with a single Field in it called ID (format as Integer) In your table of Video data add also add a field called ID (format as autonumber) If you're referring to the DVD_ID auto number, that was an oversight. That's the DVD_ID number in the bottom right of the top portion of the jewel case form. Since the DVD's hadn't been made when I started this conversion from VCR to DVD, an auto number option seemed sensible. Change the names (Surname, Initial, etc ) in this example of code to the names of your fields: actor, actor1, actor2, title, title1 etc) add additional lines if necessary. 1 line per field in the table. The last line of this list must have a ; at the end of it all the other must have an undescore _ Rename the name of your table to DNRS when you run the code, you can then look at the SearchResultsPerson which will now contain the ID of each record with a match. "Husky" wrote: On Mon, 26 Sep 2005 07:51:01 -0700, "downunderling" wrote: Husky, I am not sure if I understand your question but the following code will find any part of a string in any field in a table. It puts the ID's of each record in a table of the results. To view the result you will need to query your main table based on the ID's of the results table. In the example below this is displayed in a form called: Results (which you will need to create) I can find what I'm looking for 1 by 1, in the form with partials by using the binoculars on the toolbar. But for a printout I'll eventually need to select specific DVD's and only some fields. Obviously won't need the image for a printout. Just for the labels. I want to be able to sort and display the entire video DB by select criteria. But in the form mode, and binoculars, I only get 1 record at a time. ie: Stallone on 3 different DVD's, I want all 3 DVD's selected to print on one 8.5x11 page. Something like a report. Function Searcher(box1) Dim dbs As Database, rst As Recordset, strsql As String Set dbs = CurrentDb match = "OR" dbs.Execute " Delete SearchResultsPerson.* FROM SearchResultsPerson;" dbs.Execute "INSERT INTO SearchResultsPerson ( ID ) " _ & "SELECT DNRS.ID FROM DNRS " _ & " WHERE [salutation] Like '*" & [box1] & "*' " _ & " " & [match] & " [Surname] Like '*" & [box1] & "*' " _ & " " & [match] & " [Initial] Like '*" & [box1] & "*' " _ & " " & [match] & " [Address1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [town] Like '*" & [box1] & "*' " _ & " " & [match] & " [State] Like '*" & [box1] & "*' " _ & " " & [match] & " [Postcode] Like '*" & [box1] & "*' " _ & " " & [match] & " [Notes] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone1] Like '*" & [box1] & "*' " _ & " " & [match] & " [Telephone2] Like '*" & [box1] & "*' " _ & " " & [match] & " [Fax] Like '*" & [box1] & "*' " _ & " " & [match] & " [mobile] Like '*" & [box1] & "*' " _ & " " & [match] & " [email] Like '*" & [box1] & "*' ;" Searcher is the name of the function so you call it from the following code Private Sub Command43_Click() Dim SearchString SearchString = InputBox("Please enter the text that you would like to find ....", "Find ...") If IsNull(SearchString) Then Else If SearchString = "" Then Else Call Searcher(SearchString) DoCmd.OpenForm ("Results") End If End If End Sub Important - for this to work you will need to declare box1 as a public variable Public box1, match (place this at the begining of a module ) The code that calls the function resides in a Form (in my example it is called from the click of command button 43) If you type in rob - then it will find any combination of this word like in Robert or Robin or Wroby "Husky" wrote: You can't get there from here. Or so it seems. I got my DVD catalog and form working fine for display on the Jewel cases. square box on one side, DVD round image cover on other. The form isn't really the problem. For future reference I would like to search to find all the DVD's that actorx appears in. But to be able to enter all the different actors and titles on the DVD [think it's 4-6 hours of play] I had to go with the max number of actors [12] and create fields actor, actor1 etc.. And also did the same with titles, title, title1, etc.. But to find all the DVD's' that actor x appeared in with a query, I needed to enter the full name, [partial failed] and that only worked if I entered the query in actorx field that actorx actually appears in. ie: actor x might be in actor1 field, but if I query on field actor, nothing pops up, even though actorx is on that DVD's. Just may appear in actor 1, actor2, etc-12 fields. with 12 identical fields for actor, on each record, how can I search for all the DVD's' actor x is on ? I tried the wild card, but obviously don't understand it. -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html -- more pix @ http://members.toast.net/cbminfo/index.html |
#20
|
|||
|
|||
The typical way to represent this hierarchical relationship on a form is with
a form and subform, where the form's recordsource is your movies table and the subform's recordsource is a query that merges the actors table with the junction table (movieactors?). Then the link between the form and subform would be on the movie ID. "Husky" wrote: On Mon, 26 Sep 2005 00:41:30 -0400, "Randy Harris" wrote: Suggest you read the replies from Ed and John more carefully. You need 3 tables! You don't really need to add the character names (you could if you wanted), but think of the "junction" table, as John called it, as Roles. So you have tables: Actor Movie Role Actor will contain the names (and other details) of every actor in all the movies. Movie will contain the names (and other details you wish) of the movies. Role will contain one record for each actor appearing in a move: Example: Actor ActorID LastName FirstName DOB ------- ----------- ---------- ----- 101 Bogart Humphrey 102 Bergman Ingrid Movie MovieID Title Director PlayingTime ------- ---------- -------- ------------ 101 Casablanca 102 The Maltese Falcon Role MovieID ActorID Character -------- --------- --------- 101 101 Rick 101 102 Ilsa 102 101 What you'll likely do, is have one form to add/edit Actors. Another for Movies. And a third, where you'll select the Actor and the Movie from lists, for Roles. You will have no limits to the number of Movies, Actors or Roles. There are a great many compelling reasons for doing it this way. You will have lots of problems attempting to do it the way you are currently. This is how a relational database management system works. Hope this helps to clarify things, It's making a bit more sense. But I'm using the form as the template to print to the DVD labels. Killing 2 with one stone. It updates my video table. How can I keep the current form's design, with all the different tables and tie it all together ? -- more pix @ http://members.toast.net/cbminfo/index.html |
Thread Tools | |
Display Modes | |
|
|