Table Design Suggestions Welcome

[Murp]

I was just wondering if I could get your suggestions on the best table design
for my situation? I've gone with one, but it has been problematic.

I have one table [tblShootActor] that I want to use to list Actors' names
[ActorID], their Costume [CostumeID], and their Shoot number [ShootID]. The
actor's name is looked up in another table [tlkpActor] which stores their ID
and their name. The costume is looked up in another table [tlkpActorCostume]
which stores the costume ID, the costume description, and the actor to which
the costume applies.

The problem I am experiencing is that when I use a combo box to look up the
relevant costume for the actor in [tblShootActor], I get all costumes instead
of just those for the actor. And when I try to use a where condition in the
rowsource to filter only those for the actor, it applies that rowsource for
every record (I am viewing in datasheet view). Any suggestions would be much
appreciated.

 

[Ken Snell [MVP]]

Are you storing ActorID in tlkpActorCostume table? That table should just
store the CostumeID and related costume info; no need to double-store the
costume/actor relationship there because you already get that in
tblShootActor table.

 

[Murp]

Good point Ken, thanks for replying. I included the actor ID there so that I
could filter out who the costumes were intended for when I am adding
actors/costumes i.e. if I add "Dan", then I only want to see the costumes
made for that actor.
--
Yo Yo Ma.

Are you storing ActorID in tlkpActorCostume table? That table should just
store the CostumeID and related costume info; no need to double-store the
costume/actor relationship there because you already get that in
tblShootActor table.

--

Ken Snell
<MS ACCESS MVP>

I was just wondering if I could get your suggestions on the best table
design
for my situation? I've gone with one, but it has been problematic.

I have one table [tblShootActor] that I want to use to list Actors' names
[ActorID], their Costume [CostumeID], and their Shoot number [ShootID].
The
actor's name is looked up in another table [tlkpActor] which stores their
ID
and their name. The costume is looked up in another table
[tlkpActorCostume]
which stores the costume ID, the costume description, and the actor to
which
the costume applies.

The problem I am experiencing is that when I use a combo box to look up
the
relevant costume for the actor in [tblShootActor], I get all costumes
instead
of just those for the actor. And when I try to use a where condition in
the
rowsource to filter only those for the actor, it applies that rowsource
for
every record (I am viewing in datasheet view). Any suggestions would be
much
appreciated.

 

[Ken Snell [MVP]]

OK.

With regard to why you see all costumes, or you see same "row source" in
every record, that is because you're using a continuous forms view for the
datasheet. In continuous forms view, there is actually only one combo box
control that is displayed multiple times -- thus, whatever setting you have
for that combo box applies to all records. It's possible to use the form's
Current event to requery the combo box's RowSource query so that it shows
just the items for the actor on that specific record... sometimes, this is
sufficient for what you want/need.

--

Ken Snell
<MS ACCESS MVP>

Good point Ken, thanks for replying. I included the actor ID there so that
I
could filter out who the costumes were intended for when I am adding
actors/costumes i.e. if I add "Dan", then I only want to see the costumes
made for that actor.
--
Yo Yo Ma.

Are you storing ActorID in tlkpActorCostume table? That table should just
store the CostumeID and related costume info; no need to double-store the
costume/actor relationship there because you already get that in
tblShootActor table.

--

Ken Snell
<MS ACCESS MVP>

I was just wondering if I could get your suggestions on the best table
design
for my situation? I've gone with one, but it has been problematic.

I have one table [tblShootActor] that I want to use to list Actors'
names
[ActorID], their Costume [CostumeID], and their Shoot number [ShootID].
The
actor's name is looked up in another table [tlkpActor] which stores
their
ID
and their name. The costume is looked up in another table
[tlkpActorCostume]
which stores the costume ID, the costume description, and the actor to
which
the costume applies.

The problem I am experiencing is that when I use a combo box to look up
the
relevant costume for the actor in [tblShootActor], I get all costumes
instead
of just those for the actor. And when I try to use a where condition in
the
rowsource to filter only those for the actor, it applies that rowsource
for
every record (I am viewing in datasheet view). Any suggestions would be
much
appreciated.

 

[mnature]

There is another way to set up your tables. Have your main table be
[tblShoot], with fields of [ShootID], [ActorID], and [CostumeID]. The
[ShootID] uniquely identifies the shot, and the [ActorID] and [CostumeID]
link over to the Actor and Costume Tables. Then you can sort by Actors or
Costumes, and see the corresponding ShootID. You don't have to put the
Actors name as a field in the Costume Table, because that will be linked
through the ShootID.

 

[John Vinson]

There is another way to set up your tables. Have your main table be
[tblShoot], with fields of [ShootID], [ActorID], and [CostumeID]. The
[ShootID] uniquely identifies the shot, and the [ActorID] and [CostumeID]
link over to the Actor and Costume Tables. Then you can sort by Actors or
Costumes, and see the corresponding ShootID. You don't have to put the
Actors name as a field in the Costume Table, because that will be linked
through the ShootID.

ummm... this will allow one and only one Actor, and one and only one
Costume, per shoot. The foreign key should be in the "many" side
table(s), not the master table!

John W. Vinson[MVP]

 

[Tim Ferguson]

I have one table [tblShootActor] that I want to use to list Actors'
names [ActorID], their Costume [CostumeID], and their Shoot number
[ShootID]. The actor's name is looked up in another table [tlkpActor]
which stores their ID and their name. The costume is looked up in
another table [tlkpActorCostume] which stores the costume ID, the
costume description, and the actor to which the costume applies.

We need to know a little more about how your keys actually work. It
should be something like this (sorry for renaming, but I can only keep a
clear head when names of things mean what the things are...):-

Shoots(*SceneNumber, Location, etc etc)

Actors(*EquityNum, FName, StarRating, etc etc)

Costumes(*CostumeID, Description, WardrobedIn, etc)


now, the links should be something like:

IsFittedFor(*Actor, *Costume)
FK: Actor references Actors.EquityNum
FK: Costume references Costumes.CostumeID

AppearsIn(*Actor, *SceneNumber, Wearing, NumberOfLines, etc)
FK: (Actor, Wearing) references IsFittedFor(Actor, Costume)
FK: SceneNumber references Shoots(Scenenumber)

Note: there is NO relationship between AppearsIn.Actor and the Actors
table. The constraint is enforced via the IsFittedFor table, so you can't
have a non-existent Actor. The AppearsIn(Actor, Wearing) relationship
ensures that you can't assign Dora's dress to Eric.

As far as the UI is concerned, you'd want to set the rowsource of a combo
box to something like "SELECT Costume FROM IsFittedFor WHERE Actor=1088"
and so on.


Hope that helps


Tim F

 

[mnature]

Oops.

Main table should be linked to all other tables. So should have

[MainTable]
MainID (key)
ShootID (linked)
ActorID (linked)
CostumesID (linked)

[ShootsTable]
ShootID (key)
ShootDescription

[ActorTable]
ActorID (key)
ActorName

[CostumeTable]
CostumeID (key)
CostumeDescription

This should bring in all tables to the MainTable, so that you can have
multiple actors on any one shoot, with any of the costumes. Then you can
sort on that MainTable by Shoot, Actor, and/or Costume.

Thanks, John, for seeing that.

There is another way to set up your tables. Have your main table be
[tblShoot], with fields of [ShootID], [ActorID], and [CostumeID]. The
[ShootID] uniquely identifies the shot, and the [ActorID] and [CostumeID]
link over to the Actor and Costume Tables. Then you can sort by Actors or
Costumes, and see the corresponding ShootID. You don't have to put the
Actors name as a field in the Costume Table, because that will be linked
through the ShootID.

ummm... this will allow one and only one Actor, and one and only one
Costume, per shoot. The foreign key should be in the "many" side
table(s), not the master table!

John W. Vinson[MVP]