If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design Suggestions Welcome
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. -- Yo Yo Ma. |
#2
|
|||
|
|||
Table Design Suggestions Welcome
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 "Murp" wrote in message ... 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. -- Yo Yo Ma. |
#3
|
|||
|
|||
Table Design Suggestions Welcome
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. "Ken Snell [MVP]" wrote: 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 "Murp" wrote in message ... 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. -- Yo Yo Ma. |
#4
|
|||
|
|||
Table Design Suggestions Welcome
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 "Murp" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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 "Murp" wrote in message ... 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. -- Yo Yo Ma. |
#5
|
|||
|
|||
Table Design Suggestions Welcome
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. |
#6
|
|||
|
|||
Table Design Suggestions Welcome
On Thu, 3 Nov 2005 08:32:09 -0800, "mnature"
wrote: 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] |
#7
|
|||
|
|||
Table Design Suggestions Welcome
"=?Utf-8?B?TXVycA==?=" wrote in
: 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 |
#8
|
|||
|
|||
Table Design Suggestions Welcome
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. "John Vinson" wrote: On Thu, 3 Nov 2005 08:32:09 -0800, "mnature" wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Suggestions on a Petty Cash table design | Joe Cilinceon | Database Design | 0 | December 22nd, 2004 09:23 PM |