Main > Everything Else
Anyone good in Access that can lend a hand?
AmericanDemon:
The depth of knowledge here is sometimes very amazing to me. I know someof you have and useAccess on an almost daily basis and was hoping someone could help me out.
I've been trying to help my girlfriend with an asignment for her law office administration course. We've tried several things but can't seem to get it right. Its a relational database with the follwing base for information
Five individual cases with case number, status of case, and upcoming events. each case has a single client with contact information. Each case also has five witnesses with contact information.
We currently have three individual tables. Each has a key based on the case number ID.
Case Information:
Case Number, Status of Case, Upcoming Events
Client Table:
Case Number, Last Name, First Name, Street, City, State, Zip, Phone, Work Phone, e-mail
Witness Table:
Case Number, Last Name, First Name, Street, City, State, Zip, Phone, Work Phone, e-mail
As it stands right now the database will only allowus to have one witness for each case. Can someone help shine some light on what we are doing wrong and steer us in the right direction? We have only the case numbers linked, but we don't know where to go from here.
Thanks in advance guys.
boykster:
you should abstract your client table and your witness table away from the case table, meaning take the case number data element out of those two tables and make them independant. You want in theory for a witness to be able to be a witness for any case, and to have multiple witnesses per case. Additionally, you want a client to be a client for more than one case.
So, like I said, delete the case number from those 2 tables, add a unique ID to each table (Client number and Witness Number) and ADD 2 tables that act as lookup tables, call them
Case Client:
uniqueID, Case Number, Client Number
Case Witness:
uniqueID, Case Number, Witness Number
This way, these intermediate tables create the linkage between the case(s), the witnesses, and the clients in a non-constraining way.
You could even reduce the overhead/redundancy a bit more by combining the client and witness information into one table (same data stored in both, right?) and calling it "Contacts", and then assigning a "class" to those contacts. Class "witness" or "client" etc....
ChadTower:
She going for an MIS degree? :)
shmokes:
I don't know much about Access, but I'll give it a shot. The primary key needs to be something unique to that record. Case number ID is important for the relationship between tables, but don't make it the primary key in any table, because more the one record will obviously have the same client ID. Make a "client number" field in the client table, and a "witness number" field in the witness table. Those fields need to be the primary keys in their respective tables. You still need the case number field in each table to create the relationships, of course. It's okay for the case number field to be the primary key in the Case table, because there will only ever be one single case that has that number. In the other tables, once the case number field isn't set to be the primary key, Access won't mind if more than one record has the same number. So make it like this (Primary Key is the bolded one):
Case Information:
Case Number, Status of Case, Upcoming Events
Client Table:
Client Number, Case Number, Last Name, First Name, Street, City, State, Zip, Phone, Work Phone, e-mail
Witness Table:
Witness Number, Case Number, Last Name, First Name, Street, City, State, Zip, Phone, Work Phone, e-mail
Now set up your relationship exactly as you're already doing, i.e., associate the Case Name field in each table with a one-to-many relationship.
In my head that works. But, like I say, I know almost nothing about Access, so I may very well be completely wrong. If so, hopefully someone else here who knows what he's talking about will step in.
boykster:
Shmokes way works great if a witness will never be a witness in any other case, and if a client will never be a client for any other case. And that fits fine with this project.
Navigation
[0] Message Index
[#] Next page
Go to full version