You can't get there from here

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

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

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

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

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

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

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

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

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

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