You can't get there from here

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

You might have more luck with a 'normalized data design'

Table DVD
DVDID (Key) DVd Title
Table Actors
Actor ID(Key) ActorLastName ActorFirstName

Table DVD_Actors
(each actor can appear on one or more DVd's and Each DVD can have one or
more actors

DVD_ActorID(key) DVDID(Foreign Key to DVD table) ActorID(Foreign Key
to DVD table)

1:M to DVD table based on DVDID and 1:M to Actors based on ActorID

Now you can query to find which actor appears on which DVD's
Once you get 'all the actors working for you", then you can work out the
titles etc. the same way.
Ed Warren


"Husky" wrote in message
...
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

Having multiple fields like actor1, actor2, etc. is known as a repeating
group, and is definitely not recommended.

What you should have instead is a second table, with each actor being a row
in the second table, linked back to the first table.

Have you seen the DVD template that's downloadable from
http://office.microsoft.com/en-ca/te...55641033.aspx?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Husky" wrote in message
...
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 Sun, 25 Sep 2005 16:50:08 -0400, "Douglas J. Steele"
wrote:

Having multiple fields like actor1, actor2, etc. is known as a repeating
group, and is definitely not recommended.

What you should have instead is a second table, with each actor being a row
in the second table, linked back to the first table.

Have you seen the DVD template that's downloadable from
http://office.microsoft.com/en-ca/te...55641033.aspx?
Yes I've seen it before, spent days on trying to get it to do what I want. But
got nowhere figuring it out. Possibly something wrong as it used to work. Now
it just asks for input from the switchboard.


--
more pix @ http://members.toast.net/cbminfo/index.html

On Sun, 25 Sep 2005 15:50:02 -0500, "Ed Warren"
wrote:

You might have more luck with a 'normalized data design'

Table DVD
DVDID (Key) DVd Title
Table Actors
Actor ID(Key) ActorLastName ActorFirstName

Table DVD_Actors
(each actor can appear on one or more DVd's and Each DVD can have one or
more actors

DVD_ActorID(key) DVDID(Foreign Key to DVD table) ActorID(Foreign Key
to DVD table)

1:M to DVD table based on DVDID and 1:M to Actors based on ActorID

Now you can query to find which actor appears on which DVD's
Once you get 'all the actors working for you", then you can work out the
titles etc. the same way.
Ed Warren
Thing is I'm using the form to input the data. How can I enter 12 actors with
only 1 field called actor to enter the data in ? Ditto with title..



"Husky" wrote in message
.. .
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 Sun, 25 Sep 2005 17:42:09 -0400, Husky
wrote:

Thing is I'm using the form to input the data. How can I enter 12 actors with
only 1 field called actor to enter the data in ? Ditto with title..

"Fields are expensive, records are cheap".

If you have twelve actors in a movie you add twelve RECORDS to this
"junction" table.

You may be making the very common error of assuming that all
information about a movie must be stored in the same record of a
single table. That's not how relational databases work! If you have a
many (movies) to many (actors) relationship, you will store the
information about the movie in (at least) two tables - in this case, a
table of movie specific information (the title, studio, issue date,
etc.) and another table (which you might call Cast) in which there are
as many records as there are cast members.

John W. Vinson[MVP]

On Sun, 25 Sep 2005 17:44:18 -0600, John Vinson
wrote:

On Sun, 25 Sep 2005 17:42:09 -0400, Husky
wrote:

Thing is I'm using the form to input the data. How can I enter 12 actors with
only 1 field called actor to enter the data in ? Ditto with title..

"Fields are expensive, records are cheap".

If you have twelve actors in a movie you add twelve RECORDS to this
"junction" table.
Not entirely lost just yet.
field field
ie: table actor movie
stallone rocky 1
stallone rocky 2
DVD 1

Lets keep it real simple
from that right there I have 2 tables. titles and actors Let's replace
stallone's name, or add his girlfriends name to just the 2nd one. So I forgot
he met her in Rocky 1.
Talia Shire I think,,,

field field
table actor movie
stallone rocky 1
stallone rocky 2
Talia rocky 2
DVD 1
In previous attempts to add another actor, it usually just over wrote the 1st
one. IOW: Talia would replace stallone.
I also need this info to display on my form the way it's displaying now

title actor
title1 actor1
title2 actor2
Comments DVD #


=================================
Disk image

To make it really simple the only fields I have are actor full name, not first
and last. That just made things even more complicated.
Actor 1-12, Title 1-12, DVD #, Tower [which carousel it's located in], color
[color of case], comments [memo type], image [bitmap], and 2 sidebars where I
stuck title on the left, and the variable title and title 1 on the right
sidebar as control sources. title 1 sometimes made it too long to print
dropping back to using just the title sidebar.

How with only a single actor field in the database, can I fill 12 fields on the
form with 12 different actor names ? and ditto with the 12 title fields.
Actually I've come nowhere near 12 in titles. 8 maybe.
and here's another monkey wrench. While updating these titles and names, I
can't start with a DB of all the titles and names, because I don't know all the
names and titles. I'm entering them one by one as I extract them from the
DVD's. So a table of actors, and titles would start blank, or use what I've
already entered. And there's still more to enter.

You may be making the very common error of assuming that all
information about a movie must be stored in the same record of a
single table. That's not how relational databases work! If you have a
many (movies) to many (actors) relationship, you will store the
information about the movie in (at least) two tables - in this case, a
table of movie specific information (the title, studio, issue date,
etc.) and another table (which you might call Cast) in which there are
as many records as there are cast members.

John W. Vinson[MVP]
And will the form add to these tables ? Stupid question. It's doing that now,
but it's all in my single videos table.
If I have an actor table, a video table, a tower table, a color table..
This is reverse engineered. I have a color table [drop down list] of red,
green, blue, purple, orange already and when I enter the color in the forms
field, it fills in the color field in the videos table.
That's probably 50 megs or more right now.
Actually it was 50 last week some time.
I don't really need a color table, but it was so simple to have one, and let
the form fill in the color field in the videos table.
I will be making an alphabetical printout, minus the images, of titles, and DVD
#, and tower at a later date also.

Can I have the form like this
title actor
title actor
title actor
Comments DVD #
and have a different actor and title in each of those fields ?
Or is it going to just keep over writing ?
table actor title DVD
stallone rocky1 1
Talia rocky2 1
and check the actor table Talia's the only name, title table only has rocky2.

The videos table has all the fields from the form.

--
more pix @ http://members.toast.net/cbminfo/index.html

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,
Randy

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

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