Access 2k: Querying all fields

[Neil Marsh]

I have a feeling I all ready know the answer to this question, which
would mean I just wasted the last 8 months of my life, but I'll give
it a shot anyway:

I'm doing research for a book/website about a radio horror anthology
series. There were 100 episodes of this show, so my database has 100
records. There are 87 fields in each record, not all of which are or
will be filled. In order to create data tables for the book and
website, I need to query the database for names of writers, actors,
producers, etc. In some cases there is only one field in which a given
person's name might appear: the Production Assistant, for example. In
other cases, such as actors, the name could appear in any one of 15
fields, depending on the order in which their name appears in the
credits of the story. There were also writers who doubled as producers
and actors who doubled as writers.

So I'd like to be able to query all fields and have the result be a
table of all the records that name appears in. Then I can just cut &
paste the contents into the book or website files.

I've seen it said in various places that this is not possible with
Access. I can't imagine that no one at Microsoft ever thought that
someone might want to search all fields for a particular piece of
data. How hard would it be to have the Query process allow such a
search?

Is there a solution for this? I really don't want to have to create
hundreds of queries with 15+ fields and a name in each one.

Thanks in advance.

-/\/

---
Neil Marsh * (e-mail address removed)
Cambridge, MA * http://AudioBoy.net

"In the dream you are falling, lost in the listening distance as dark
locks in ... Nightfall!"

 

[Joe Fallon]

It sounds like you built everything into a single table.
This is not a good idea for Access.

You should use many tables that are related to each other.
Then queries will work better.

You should get some help on designing a relational database.
(Books or an expert.)

 

[John Vinson]

I have a feeling I all ready know the answer to this question, which
would mean I just wasted the last 8 months of my life, but I'll give
it a shot anyway:

Well, you've come up with a non-normalized design which makes your
query much more difficult than it should be - but at least the data in
your table can be salvaged, by migrating it into a different table
structure.

I'm doing research for a book/website about a radio horror anthology
series. There were 100 episodes of this show, so my database has 100
records. There are 87 fields in each record, not all of which are or
will be filled. In order to create data tables for the book and
website, I need to query the database for names of writers, actors,
producers, etc. In some cases there is only one field in which a given
person's name might appear: the Production Assistant, for example. In
other cases, such as actors, the name could appear in any one of 15
fields, depending on the order in which their name appears in the
credits of the story. There were also writers who doubled as producers
and actors who doubled as writers.

You're using a relational database, and you'll have a LOT better luck
if you use it relationally, rather than as if it were a spreadsheet!

In a relational database, "fields are expensive, records are cheap".
The place to start with table design is to identify the "Entities" -
real life persons, things, or events - of importance to your
application; each type of Entity should be modeled by its own table.
For instance, you have Episodes, a perfectly valid entity; but you
also have People, Roles (e.g. Production Assitant), and probably other
entities. Rather than having each person's name appear over and over
in multiple fields, in multiple records, a better design would be to
have each person's name appear ONCE, and once only, in a table of
People; and use Access' relational features to link to it. I'd suggest
the following tables:

Episode
EpisodeID <perhaps an autonumber, or a date if each episode was
aired on one and only one day> <Primary Key, abbreviated PK>
EpisodeDate
Title
<other info about the episode, NOT including credits>

People
PersonID Autonumber PK <names are NOT unique and should not be keys>
LastName
FirstName
<other bio information about the person if needed>

Roles
RoleID Autonumber PK
Role <e.g. "Production Assistant"

Credits
EpisodeID <link to Episodes> <PK, joint with...>
CreditLine <position in the credit order, two-field PK>
PersonID <link to People>
RoleID <what role this person played in this episode>

The Credits table will allow the same person to appear as (say) a
writer, producer, and actor in the same episode, by adding three
records for that person.

Perhaps you would also want a table of Characters, and a table of
CharacterAppearance with the episode in which the character appears
and the PersonID of the actor voicing that character.

Note that the data in your current table is still perfectly good and
usable - you could run a series of Append queries to extract all the
names from your many fields into separate rows in the Names table
(this will require some work to make sure that "Mark Jones" and "Marc
Jones" end up as different records if they are different people, and
only one record if it's just a spelling difference); you can then run
additional queries to populate the Credits table.

So I'd like to be able to query all fields and have the result be a
table of all the records that name appears in. Then I can just cut &
paste the contents into the book or website files.

It's NOT necessary to create a Table in order to do this. You can
export or link to a Query joining the tables. This design puts all the
people's names in *just one field*; you can create a variety of
queries linking the various tables, and (for instance) find all the
episodes where Joe Jones appeared in any role, or where Joe Jones was
the director, or any other combination.

I've seen it said in various places that this is not possible with
Access. I can't imagine that no one at Microsoft ever thought that
someone might want to search all fields for a particular piece of
data. How hard would it be to have the Query process allow such a
search?

You can search up to 32 fields in one pass, at least - possibly more;
I'm not sure why you insist that you can't.

Is there a solution for this? I really don't want to have to create
hundreds of queries with 15+ fields and a name in each one.

There is also a Parameter query: you can create one query with

[Enter name:]

on the criteria line (or on 32 criteria lines, so it uses OR logic)
and use that query even with your current design. On the other hand, a
normalized structure as suggested above will let you slice & dice the
data much more flexibly!