yet another Multi-select Listbox ??

[wannabe]

I have been searching the site for 2 days now, hoping to find someone out there trying to attempt the same thing as I am. Now I'm beginning to think that I'm SOL.
I have written a program for our local Public Defender's Office. so the attorneys can track their time along with other itmes regarding a Defendant and his or her charges. I have created a form that shows all of the information about a defendant's case, like when it was filed, how much the bond it, if they are in custody or have posted bond, etc. Then I have a subform to display the 1 to many charges.
The problem I have run into is that in order to normalize my charges table, I have created another table (a lookup table) to handle the Attempted, Alternative, Conspriacy to commit, Common Scheme, , ect. that could go with a charge. ie: Attempted Theft. That way I don't have Theft listed in my Charges table numberous times to handle all of the variations. So I have put these "variation" in a list box with multi select set to simple. Because there could be more than 1 "variation" for a charge. ie: Attempted Theft a Common Scheme.

Now on to the problem. I have a join table set up to handle this so that I don't have a many to many relation. However, I don't know how to store whatever is selected in the list box in this table. Am I totally off my rocker, or can this be done.

Any help would be greatly appreciated.
TIA,
Emily

 

[wannabe]

Thanks Pat,
That's the way I wanted to do it in the first place, but had a co-worker tell me, NEVER create a many to many relationship.
So I will try it that way and see what happens.
Emily

 

[wannabe]

Thanks Pat,
That's the way I wanted to do it in the first place, but had a co-worker tell me, NEVER create a many to many relationship.
So I will try it that way and see what happens.
Emily

 

[directormac]

I would use a many-to-many relationship because that is what your application calls for.

Pat: If you have a moment, would you expand on that? I can see either tblDefendants having TWO fields (lngChargeModifierID and lngChargeID) or one field that points to a value in a junction/look-up table which stores the possible combinations of the charges and modifiers. Are either of these what you had in mind?

--Student Mac

 

[directormac]

Got it--my mental lapse was in not envisioning a three-way juntion table. Charges & details junction I got, but not the best way to relate both to a single defendant (alternately, defendant & charge I got, but not the best way to factor in detail...)

Actually, won't there also need to be a date field as part of a complex PK in that junction table? Or perhaps a Case #? Because the same defendant could be up for the same charge & charge detail more than once, right?

Thanks again for making these forums such an incredible resource!

--Grateful Mac