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
On Mon, 26 Sep 2005 06:59:31 -0500, "Ed Warren"
<
[email protected]>
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.
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 ?
