Paul,
What John Spencer suggests is probably what I would do with your
Table (that is, to put all the names into separate Tables and link
them). I'm afraid that my original verbal explanation wasn't very
clear. I usually try to include examples, and I didn't have time to do
that. So, here are my examples, comparing three possible designs (but
others are also possible).
You could, if you wish, just use a flat Table (no links), and it
would work, but it would probably take more total space than linked
Tables and, more importantly, if you noticed that one of the names were
misspelled, you'd have to change all instances of that name in a flat
Table, whereas in a linked Table you'd change it just the one place it
appears. Using links (references) also allows you to specify
Referential Integrity, which lets you, for example, automatically delete
all the movies listed for Batman just by deleting the Batman record. In
a flat Table you'd need to delete them individually.
Here's the flat Table, in which each field contains a text string.
[Favorite movies_Flat] Table Datasheet View:
Name Favorite movie
---------- --------------
Batman Batman
Batman Batman Begins
Batman Batman Forever
Batman Batman Returns
Batman Ghostbusters
Spider-Man Spider-Man
Spider-Man Spider-Man 2
An example of a Table design you should probably AVOID is one containing
"repeated groups" (but in this case each "group" is just one field),
something like this. The "repeated group" is a field (or set of fields)
which is essentially identical to some other field in the same record,
except that it might contain a different value.
[Favorite movies Repeated] Table Datasheet View (but try not to use this
kind of design):
Name FM1 FM2 FM3 ...
---------- ---------- ------------- --------------
Batman Batman Batman Begins Batman Forever ...
Spider-Man Spider-Man Spider-Man 2
You might find a Table with repeated groups in a spreadsheet, and in a
spreadsheet it probably wouldn't cause problems. There's nothing
illegal about repeated groups (Access won't give you an error message
about them), but they make analysis difficult. (See the [Q_Movies
Number] Query, below.)
The "many-to-many" design that John Spencer suggested might look like
this, with additional Tables for the names of characters and movies.
Each of the new Tables includes an Autonumber field to serve as its
primary (unique) key.
[Names] Table Datasheet View:
Names_ID Name
---------- ----------
648041108 Batman
-1788904443 Spider-Man
[Movies] Table Datasheet View:
Movies_ID Favorite movie
---------- --------------
-1590704580 Batman
-1535131507 Batman Begins
-743209270 Batman Forever
-1037554301 Batman Returns
1270067720 Ghostbusters
445217641 Spider-Man
-1135933578 Spider-Man 2
Now, instead of names, the [Favorite movies] Table contains key values;
in this case, each key is a Long Integer (32 bits) matching one of the
keys in [Names] or [Movies].
[Favorite movies] Table Datasheet View:
Names_ID Movies_ID
----------- ------------
648041108 -1590704580
648041108 -1535131507
648041108 -743209270
648041108 -1037554301
648041108 1270067720
-1788904443 445217641
-1788904443 -1135933578
If you wish, you can hide the raw keys in this Table by defining Lookup
properties on them (making its Datasheet View look just like
[Favorite movies_Flat] Table's Datasheet View), but you can also define
a Query to do that, like this:
[Q_Favorite Movies] SQL:
SELECT Names.Name, Movies.[Favorite movie]
FROM ([Favorite movies] INNER JOIN Movies
ON [Favorite movies].Movies_ID = Movies.Movies_ID)
INNER JOIN [Names]
ON [Favorite movies].Names_ID = Names.Names_ID
ORDER BY Names.Name, Movies.[Favorite movie];
The result looks just like the original [Favorite movies_Flat] Table's
Datasheet View.
[Q_Favorite Movies] Query Datasheet View:
Name Favorite movie
---------- --------------
Batman Batman
Batman Batman Begins
Batman Batman Forever
Batman Batman Returns
Batman Ghostbusters
Spider-Man Spider-Man
Spider-Man Spider-Man 2
Although the datasheet looks identical, it doesn't work exactly like the
[Favorite movies_Flat] Table's Datasheet. You could use either one in a
List Box control on a Form. However, if you try to change a name in
Datasheet View, the [Favorite movies_Flat] version would let you edit
that one name, whereas the [Q_Favorite Movies] version would change all
instances of the name you changed to match the changed version.
I mentioned that the repeated groups in [Favorite movies Repeated] Table
might make analysis difficult. Here's an example of that. Suppose you
wanted to list the number of movies in your Table for each of the
characters named there, five for Batman and two for Spider-Man. With a
linked Table, you could do that with the following Query:
[Q_Movies Number] SQL:
SELECT Names.Name,
Count([Favorite movies].Movies_ID) AS [Number Of Movies]
FROM [Favorite movies] INNER JOIN [Names]
ON [Favorite movies].Names_ID = Names.Names_ID
GROUP BY Names.Name
ORDER BY Names.Name;
[Q_Movies Number] Query Datasheet View:
Name Number Of Movies
---------- ----------------
Batman 5
Spider-Man 2
With a flat Table, I'd probably use two Queries to accomplish the same
thing, such as these:
[Q_Movies_Number Flat] SQL:
SELECT Q_Names.Name,
Count([Favorite movies_Flat]![Name]) AS [Number Of Movies]
FROM Q_Names INNER JOIN [Favorite movies_Flat]
ON Q_Names.Name = [Favorite movies_Flat].Name
GROUP BY Q_Names.Name
ORDER BY Q_Names.Name;
[Q_Names] SQL:
SELECT DISTINCT [Favorite movies_Flat].Name
FROM [Favorite movies_Flat];
The results would be the same as in the [Q_Movies Number] Query
Datasheet. So, you could use a flat Table, but it would make this
particular Query a bit more complex.
A Table with repeated groups, however, such as my [Favorite movies
Repeated] Table, would be much more difficult to analyze this way.
Given such a Table, I probably would not even try to write a Query
directly addressing it, but would instead write Queries that would
display its contents in a form looking more like the [Names], [Movies],
and [Favorite movies] Tables. I would then do the analysis based on
those Queries. (Notice that, in my [Q_Movies_Number Flat] Query, I did
something like that -- I used the [Q_Names] Query to simulate the
[Names] Table, but without using any acutal Table besides [Favorite
movies_Flat].)
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
John said:
This seems like a many to many issue. That is one person can have many (0
to 5) favorite movies and one movie can be a favorite for many individuals.
This implies three tables (especially if you want to store details about the
individual and the movies).
Table: Individual with fields
--PersonPk (unique identifier)
--LastName
--SecretIdentity
Table: Movies
--MoviePK(unique identifier)
--Title
--ProductionCompany
--Rating
Table: FavoriteMovies (one record for each person + Movie combination)
--PersonID (a copy of the PersonPk)
--MovieID (a copy of the MoviePk)
To get your list of people and movies you would have a query like:
SELECT LastName, Title
FROM (Individual INNER JOIN FavoriteMovies
ON Individual.PersonPK=FavoriteMovies.PersonID)
INNER JOIN Movies
ON Movies.MoviePK =FavoriteMovies.MovieID
I use Access 2003. What I want to do seems somewhat complicated to me, so
I'm
not even sure if it is possible. I've created a test database of movies to
try things out that may be needed for the databases needed for my work.
One
table has movie titles, stars, etc. The next thing I want to try is to
create
a table for people to list their five favorite movies. If it is even
possible, how could I set up that table so that it can link to the movies
table?
For example, say Batman's favorite movies were Batman, Batman Begins,
Batman
Returns, Batman Forever, and Ghostbusters [even he didn't like Batman and
Robin ;-) ]... Then, say Spider-Man only listed two favorite movies,
Spider-Man and Spider-Man 2. I'd want to be able to do a query and have
Batman linked to all five of those movies. Below is one example of what I
might want to see in the query:
Name: Favorite movie:
Batman Batman
Batman Batman Begins
Batman Batman Forever
Batman Batman Returns
Batman Ghostbusters
Spider-Man Spider-Man
Spider-Man Spider-Man 2
Is this, or something close to this possible? If so, how would I go about
doing this?
--
Have a nice day!
~Paul
Express Scripts,
Charting the future of pharmacy
~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor
