Tables and Queries

[Trish]

I am using Access 2000. I am working through a past exam paper to do with
integrating data. Initially I have to set up two tables in Access. The
first is called Costumes, with the following headings:

Activity Name Cost for Actor Hire time Costume Hire Only Description

I only want to concentrate on the Activity Name for the moment, which is the
primary key. The data is:
Spiderman
Alice in Wonderland
Peter Pan
Clown
Lion King
Madonna
etc

The second table is called Actors, which has a number of fields, including
Activity Name (Primary Key). The data is this field contains more than one
activity, separated by a comma, eg Spiderman, Peter Pan.

I have set up a relationship between Costumes and Actors, using Activity Name.

All of the above are givens, ie they are stated in the exam paper.

The first query I have to do is to find ALL information using Activity Name,
Cost for Actor, Hire Time and Costume Hire Only. It returned four results,
whereas there are ten records. Any idea where I went wrong?

The second query is even more troubling ... I have to find out the names and
addresses of any actors who can perofrm the Peter Pan party game. This
query only returned one, which is the instance where Peter Pan is on its own.
The Actors table had two records that showed Peter Pan with another
activity, separated by a comma, and these were not returned. I have tried
several things in the criteria, to no avail.

I know this is lengthy and longwinded, but I wanted to give you as much
background as possible. I really would like to get this right and
understand it properly.

I look forward to an early response! Thanks in advance.

 

[David Lloyd]

Trish:

In your Actors table, you should create separate rows for each Activity,
rather than a comma separated list. Because there will be duplicates in the
Activity Name in this table, you should not use it as the primary key (you
can use an autonumber field instead).

Really you should have three tables. One for Costumes, one for Actors, and
one for Activities which shows which activities each actor performs.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am using Access 2000. I am working through a past exam paper to do with
integrating data. Initially I have to set up two tables in Access. The
first is called Costumes, with the following headings:

Activity Name Cost for Actor Hire time Costume Hire Only Description

I only want to concentrate on the Activity Name for the moment, which is the
primary key. The data is:
Spiderman
Alice in Wonderland
Peter Pan
Clown
Lion King
Madonna
etc

The second table is called Actors, which has a number of fields, including
Activity Name (Primary Key). The data is this field contains more than one
activity, separated by a comma, eg Spiderman, Peter Pan.

I have set up a relationship between Costumes and Actors, using Activity
Name.

All of the above are givens, ie they are stated in the exam paper.

The first query I have to do is to find ALL information using Activity Name,
Cost for Actor, Hire Time and Costume Hire Only. It returned four results,
whereas there are ten records. Any idea where I went wrong?

The second query is even more troubling ... I have to find out the names and
addresses of any actors who can perofrm the Peter Pan party game. This
query only returned one, which is the instance where Peter Pan is on its
own.
The Actors table had two records that showed Peter Pan with another
activity, separated by a comma, and these were not returned. I have tried
several things in the criteria, to no avail.

I know this is lengthy and longwinded, but I wanted to give you as much
background as possible. I really would like to get this right and
understand it properly.

I look forward to an early response! Thanks in advance.

 

[Trish]

David, thank you. I absolutely agree with you, and that is how I would have
done it. However, as I stated in my first posting, these are givens, ie
that is how it is shown in the exam paper, and I have to print out a copy to
show that I have done it that way. It actually tells me to set up the tables
(as printed in the exam paper) and states which keys to use as the primary
keys, and to set up the relationship on that. I have never seen this before!
Hopefully I won't get something quite so wierd when the real exam occurs.
Thanks for your assistance anyway.
Trish