Forms design problem

[RipperT]

I have the following tables set up, (many fields are omitted for simplicity)
but am confused about how to do one thing.



First, tblMisconduct data is entered in a form, then, in a subform,
tblCharges data is entered, then in a subsubform tblSanctions data is
entered. (These nested forms are in datasheet view). There may or may not be
an AdSeg designation associated with a charge. If there is (tblAdSeg.AdSegID
is one to many into tblCharges.AdSegID), I cannot figure out a way to allow
the user to get the AdSegID into the each of the tblCharges records to which
it applies. I would like this to happen 'invisibly'; the user shouldn't see
the AdSegID, but should use other fields, the main one being the AdSegdate.
What's an easy way to accomplish this? I think I've included all the info
nessesary here, if not, please reply and I will add more. Many thanks.



tblInmates

PK InmateId



tblMisconducts

PK MisconductID

FK InmateId



tblCharges

PK ChargeID

FK MisconductID

FK AdSegID



tblSanctions

PK SanctionID

FK ChargeID

Sanction

StartDate

EndDate



tblAdSeg

PK AdSegID

AdSegDate

 

[Ron2005]

The design of the tables makes sense UNTIL you get to the AdSegID in
the Charges table. It would seem that AdSegDate should simply be part
of the table. I cannot see any logic as to why it is a separate table,
containing essentially only date nor in your description where it is
added or to what purpose. If it just represents the date added, then
just put the date there.

Any query looking for activity for a particular date, can search the
charges just as easily and perhaps even more easily than searching yet
another table.

If the tblAdSeg table is just a table of dates on which activities
occured for perhaps searching multiple tables, just have it be a table
of dates and also put the date into the places you were putting the
AdSegID. Then you can still link all the tables just as if they had an
ID but use the date as the Key.

Ron

 

[Ron2005]

I just thought of asking about that last tblAdSeg table. Is it
supposed to be a CHILD of the charges table? If it is then the keys are
switched, and the AdSeg table is the one that should have the charges
table key in it instead of the way you have it now.

Ron

 

[RipperT]

Ron, I appreciate your quick responses and your help.

One instance (record) of tblAdSeg can be associated with one or more charges
(also on one or more misconducts, but all on the same inmate), but there are
other attendant bits of info that go along with that one instance of AdSeg
(the fields that I left out). This is why a simple table of dates won't do.
So I created tblAdSeg with an AdSegID that will uniquely identify an inmates
particular instance of AdSeg classification.

It's entirely possible (nay, likely) that I am making this way more
complicated than needed. I feel like I'm going in circles with it.

Thanx again.

Rip