Re: You can't get there from here
- From: Husky <cbminfo@xxxxxxxxxxxxx>
- Date: Mon, 26 Sep 2005 09:15:27 -0400
On Mon, 26 Sep 2005 06:59:31 -0500, "Ed Warren" <eowarren@xxxxxxxxxxxxxxx>
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" <cbminfo@xxxxxxxxxxxxx> wrote in message
>news:4j1fj15tompirvin7eh05idvvarm9b1mlm@xxxxxxxxxx
>> On Mon, 26 Sep 2005 00:41:30 -0400, "Randy Harris" <randy@xxxxxxxxxx>
>> 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
.
- References:
- You can't get there from here
- From: Husky
- Re: You can't get there from here
- From: Ed Warren
- Re: You can't get there from here
- From: Husky
- Re: You can't get there from here
- From: Husky
- Re: You can't get there from here
- From: Randy Harris
- Re: You can't get there from here
- From: Husky
- Re: You can't get there from here
- From: Ed Warren
- You can't get there from here
- Prev by Date: Re: Turning off all fields.
- Next by Date: Re: use 2 rows (or larger space, somehow) for column name
- Previous by thread: Re: You can't get there from here
- Next by thread: Re: You can't get there from here
- Index(es):
Relevant Pages
|
