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

 

[Pat Hartman]

There are reasons to de-normalize a database but de-normalizing a database because you think it will be easier never works. I would use a many-to-many relationship because that is what your application calls for. If you want to reduce the number of selections in a combobox, then use two combos with the first qualifiynig the second.

If you start mushing multiple values into a single field you will be SOL when it comes to reporting. Do the application correctly now even though it seems like the long way, you'll be much happier when you get to reporting and enhancments.

 

[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

 

[Pat Hartman]

I wouldn't buy a bridge from that coworker Perhaps you could recommend some light bedtime reading for him from the following:

http://support.microsoft.com/support/kb/articles/Q288/9/47.ASP

There are three basic types of relationships, 1:1, 1:M, M:M and you don't "choose" one over another. Your application's business rules and data relationships dictate which one you need. They are not optional nor are they interchangeable.

 

[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

 

[Pat Hartman]

tblDefendents does not have ANY field that relates to charges. There is a defendent table and a charges table. They have a many-to-many relationship. Many defendents may be charged with the same crime and the same crime may have been committed by many defendents. To support this relationship, you need a third table, frequently called a junction or relation table. This table contains the primary keys from the two tables in a single record thereby "relating" them. So the tblDefendentCharge would contain:

DefendentID
ChargeID
ChargeDt
other details....

 

[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

 

[Pat Hartman]

Yes, that's why I listed the three specific fields. Date might be insufficient because I suppose someone could be charged with the same offense more than once in a day. Date/Time would do though.