two keys for a child of a child table?

[lou zion]

hi,

can somebody tell me what's wrong here? these tables represent pretty much
the structure i'm trying to get, but details are different. my tblLines
here lists the characters for every script, not just the one that matches
it's scriptname.

Table: tblScript tblCharacters tblLines
--------- ------------- ---------
Fields:key: Name <----->oo ScriptName <----->ScriptName ::note two fields
for primary key
................. Director...............key: CharID<----->CharID
..................Producer.............Character................Line

the idea here is i have lots of scripts, but they don't depend on anything
so that table is the parent, effectively. for each script i have lots of
characters, and each character has lots of lines.

so, i enter a script, then it's characters and then go enter lines one by
one.

if i just go to datasheet mode in the tblLines, choose a script, i'm getting
characters for every script, and the the number of times each script appears
matches the number of characters in the script.

is there a better way to set this up in my design so in my datasheet of
tblLines it only lists the characters for the script i've chosen?

i don't know if this makes a difference in the design, but lines will be
entered as they occur in a script, not for a single character, then another
character, but rather char A, char B, Char A, Char C, etc.

thanks a ton in advance!

lou

 

[Jeff Boyce]

Lou

If you use ScriptID and CharacterID as a two-field primary key for your
tblLine, you can only have one record (i.e., Line) per Script/Character.

By the way, the word "Name" (?your primary key field name for Script) is a
reserved word for Access. Using this as a fieldname will only confuse both
you and Access -- try ScriptName or some other more description title.

It sounds like you are working directly in the tables -- this is not a good
idea for data entry. Access tables store data (in a
not-known-to-most-humans arrangement), where forms display data. Use forms
instead.

And back to the structure ... if you want/need a sequence order to lines,
you need to include a sequence order field.