Table Relationship (2FK to 1PK)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table "tblTools" with fldtoolID as Pk and I have "tblAssembly" table that look for the value on tblTools
See the following for details

tblTools (other fields are omitted for clarity
- fldToolID (pk
- fldType (Male or Female
- fldDes

tblAssembl
- fldAssNum (pk
- fldDat
- fldMale(fk) ----> to fldToolId of tblTool
- fldFemale(fk) ----> to fldToolId of tblTool

as you can see that both fldMale and fldFemale on tblAssembly use fldToolID to refer to the tblTools
Is this design acceptable? if yes, how to create a query to tells the fldDesc for each tooling on tblAssembly
or should I split the tblTools into 2 tables, male and female? (this will result in more complex coding during data entry

any ideas

TIA
Djoez
 
Djoezz,

I do not see the reason why you have done it this way. As I understand
it, the concept here is that there are more than one Tool for each
Assembly. I don't think your idea of haviong a MaleTools table and a
FemaleTools table will work. Instead, you should have a AssemblyTools
table, so like this...

tblTools (other fields are omitted for clarity)
- fldToolID (pk)
- fldType (Male or Female)
- fldDesc

tblAssembly
- fldAssNum (pk)
- fldDate

AssemblyTools
- AssemblyToolsID
- AssNum
- ToolID
 
Thanks for the quick reply Steve

Every assembly use only 1 male tool and 1 female tool so it is not really many tools, so I thought of just putting them together in one table. And in fact ( I haven't told you this), I also have to monitor their performance for tools pairings (male and female)

Cheers
Djoez

----- Steve Schapel wrote: ----

Djoezz

I do not see the reason why you have done it this way. As I understan
it, the concept here is that there are more than one Tool for eac
Assembly. I don't think your idea of haviong a MaleTools table and
FemaleTools table will work. Instead, you should have a AssemblyTool
table, so like this..

tblTools (other fields are omitted for clarity
- fldToolID (pk
- fldType (Male or Female
- fldDes

tblAssembl
- fldAssNum (pk
- fldDat

AssemblyTool
- AssemblyToolsI
- AssNu
- ToolI
 
Djoezz,

Thanks for the further explanation. However, I would still do it the
way I suggested before. I am not sure exactly what the performance
monitoring involves, but I would imagine it can be done more easily with
the design I suggested.
 
You can add the Tools table a second time to the query grid - one is linked
to the fldFemale, and the second one is linked to the fldMale

--
Joan Wild
Microsoft Access MVP

Djoezz said:
Thanks for the quick reply Steve,

Every assembly use only 1 male tool and 1 female tool so it is not really
Click to expand...
many tools, so I thought of just putting them together in one table. And in
fact ( I haven't told you this), I also have to monitor their performance
for tools pairings (male and female).
 
Steve
Your suggestion is good for pairings that doesn't change often so user can use assembly tool number as reference but what if the pairing change say every 2 weeks? since users have to create assemblytools first before they can make the assignment. Does it still applicable

Thanks
Djoez


----- Steve Schapel wrote: ----

Djoezz

Thanks for the further explanation. However, I would still do it th
way I suggested before. I am not sure exactly what the performance
monitoring involves, but I would imagine it can be done more easily with
the design I suggested
 
Djoezz,

Yes, it is still applicable... it's just a data entry event to record
the Tools associated with the Assembly, so from this point of view, this
is just as easy whether it's in two fields in the same record, or in the
same field in two records in a related table.
 
Back
Top