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
|
|||
|
|||
Two one to many's on one table
This is not a typical business application so I don't know how easy it will
be to help. I'll do my best to explain it. If further info is needed, let me know. And thanks! tblInmates PK InmateID tblHearings PK HearingID FK InmateID tblCharges PK ChargeID FK HearingID tblSanctions PK SanctionID FK ChargeID Each inmate can have mutiple hearings; each hearing can have multiple charges; each charge can have multiple sanctions. Now, once an inmate accumulates too many charges in a short period of time, he gets classified to segregation. He can later be released from segregation and later reclassified again. I need a table to track these seg classifications, but I am stuck on how to relate them to the charges table. Ideally, a single seg classification (one) would have multiple charges (many), but the charges already exist as children of tblHearings and could not be added as children of the parent tblSegregation. How can I relate tblCharges and tblSegregation so I can extract an inmates seg classifications and the charges that go with them? Many thanks, Ripper |
#2
|
|||
|
|||
Two one to many's on one table
On Wed, 3 Dec 2008 18:35:43 -0500, "RipperT"
wrote: Your last sentence (no pun intended) made the most sense: what is it that you are trying to accomplish with this app. In most larger databases there are relations that COULD be created, but they don't further the objectives of this application, so the smart programmer does not create them. If you want to track which inmate has segregations, and which charges led to those segregations, the database design should include: tblSegregations PK SegID FK InmateID SegDate NumberOfDays tblChargesThatLedToSegregations PK SegID PK ChargeID So with that last table you have a classic Many-to-Many relation between Charges and Segregations. -Tom. Microsoft Access MVP This is not a typical business application so I don't know how easy it will be to help. I'll do my best to explain it. If further info is needed, let me know. And thanks! tblInmates PK InmateID tblHearings PK HearingID FK InmateID tblCharges PK ChargeID FK HearingID tblSanctions PK SanctionID FK ChargeID Each inmate can have mutiple hearings; each hearing can have multiple charges; each charge can have multiple sanctions. Now, once an inmate accumulates too many charges in a short period of time, he gets classified to segregation. He can later be released from segregation and later reclassified again. I need a table to track these seg classifications, but I am stuck on how to relate them to the charges table. Ideally, a single seg classification (one) would have multiple charges (many), but the charges already exist as children of tblHearings and could not be added as children of the parent tblSegregation. How can I relate tblCharges and tblSegregation so I can extract an inmates seg classifications and the charges that go with them? Many thanks, Ripper |
Thread Tools | |
Display Modes | |
|
|