Lookup problem

[Pic]

I have a database that tracks my writings. The main table contains,
among others, the following fields.

WorkID
Title
Genre

The Genre can be Play, Musical, Lyric, Monologue or Other.

My plan is to make forms showing details for each genre.
For Lyrics, I'd like to show which Musical, if any, a lyric comes
from.
For Musicals, I'd like to show a list of all the Lyrics contained in
that Musical.

I created two queries: one listing all musicals, another listing all
lyrics. I hoped to use these as sources for those two bits of
information. When I try to use those queries as lookup table sources,
I get an error message indicating that I can't make a lookup query
from the same table as the field I'm trying to put data into.

Any suggestions about a better way to achieve what I'm trying to do
will be greatly appreciated.

Thanks in advance,
Pic

 

[Jeff Boyce]

Not really sure I understand your design, but the error message you reported
makes it sound like you are trying to put more than one fact in a field.
And it also implies that you are trying to do a lookup of a lookup.

Just a hunch, but do a search on "cascading combo boxes" and see if this
isn't what you are trying to do.

Also, before you push further on this, step back and diagram the
relationships -- your explanation seems to indicate that the Genre "Lyrics"
has related "Musicals", and the Genre "Musicals" has related "Lyrics". Is
this a many-to-many relationship?

Your post also implies that you are using the "lookup" data type in your
table definition -- if so, review this newsgroup's (tablesdbdesign) postings
on the topic -- there are a LOT of reasons not to use this "feature" in your
table definitions.