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.

[CheffoJeffo]

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.

 

[shmokes]

Yeah . . . what Boykster said sounds MUCH more like the way I learned it way back when.  I've had to learn the very very basics of Access twice in my life (for a class), but I've never actually had to use it in the real world.  What Boykster said is exactly what I wanted to say, but could not fully retrieve all the necessary info from the deep recesses of my memory.   

Like Boykster said, it sounds like for this assignment you can probably get by with doing it my way, but if this is stuff that she really ought to learn, don't do it.  It's kind of a tough concept to wrap your head around at first, but once it clicks, it clicks.  Do it my way and it's not going to fully click.

edit: Oh yeah . . . it just occurred to me that what I said probably doesn't make much sense because my post came after Boykster's.  Actually, he hadn't posted when I started typing, and then when I went to post it said someone else had already posted, but I just saw Chad's "MIS" comment.  Had I seen Boykster's I probably would have just scrapped my post. 

[boykster]

I'll admit it, I'm a ringer; I've done database design for over a decade for all kinds of data systems, specifically focused on scalable datasystems for diverse data models. 

Shmokes's approach is perfectly valid for a small system and creates much simpler business rules to work with, but a scalable solution would normalize it even more than my first suggestion - have a single "contacts" table, abstract the addresses into a seperate address table, etc.  That way a witness/client/etc could have more than 1 address, etc.

You should have some good stuff to work from in this thread though 

[ChadTower]

I'll admit it, I'm a ringer; I've done database design for over a decade for all kinds of data systems, specifically focused on scalable datasystems for diverse data models. 

It should also be noted neither the question, nor the discussion, have anything to do with Access.  It is all about schema design.  That is a development issue you may not expect an MIS grad to know intimately.

[ChadTower]

Maybe they don't know how to use -your- weird database system, but they'd understand the principles here thoroughly.

Schema design is common across any DBMS.  It is independent of application.  The only thing that differs, really, from Access to Oracle is economy of scale, which is what boykster was talking about.

[boykster]

Maybe they don't know how to use -your- weird database system, but they'd understand the principles here thoroughly.

Schema design is common across any DBMS.  It is independent of application.  The only thing that differs, really, from Access to Oracle is economy of scale, which is what boykster was talking about.

Yep, I agree.  the details will be different (what datatype will the indentity key be, do you need to manually creat the trigger to autoincrement or is it handled by the db engine, how do you create the queries to join the tables and build the model of the data, etc.) but the overal idea is the same. 

I actually avoid Access like the plague; unfortunately the institute that I work at has a ton of small access 'databases' floating around that we need to get data out of and into a real RDBMS and that often involves a lot of cleanup of ugly schemas and data structures. 

Friends don't let friends use Access!!!!

[patrickl]

Friends don't let friends use Access!!!!

Well I'd rather they use Access than Excel.

[ChadTower]

Well I'd rather they use Access than Excel.

Excel isn't a database engine.  That said, I see a lot of managers try to use it as one.

[boykster]

Depending on how loose of a definition you use for "database", then sure Excel is one, but it's definately not a database engine. 

Well I'd rather they use Access than Excel.

I dunno; in some ways I'd rather they used Excel....they can't get into as much trouble with Excel as they can in Access.  I've seen some really scary access applications.

The problem with desktop db systems (access/filemaker pro/ foxpro / etc) is that someone will build something that's a barely adequate solution, and then it will grow out of control.  They're rarely documented well, if at all, and usually built by someone who knows a very little about data design and very little about programming.  We've got a system here where I work that is the biggest monster access database I've ever seen.  The code is so spaghetti, the business logic so convoluted and buried into the UI, and the schema design makes frankenstein's monster look like a supermodel.  And this system is the heart of the business process for one of the key departments where I work.  At least they dumped the tables into a MSSQL backend.

[ChadTower]

Depending on how loose of a definition you use for "database", then sure Excel is one, but it's definately not a database engine. 

You could apply that logic to a pen and a piece of paper, too.  At some point you have to draw a line between a table or two and an actual working database (even if you have to call Access a working database).

[boykster]

Oh, and yes I'm well aware of that

a) there actually ARE good Access developers out there who know how to design and build a reasonable application using Access, but they generally are aware that it is a great tool for prototyping and building small purpose built data applications

b) not all departments have an on-staff team of software architects and developers to build them apps all day long.

Access isn't evil, but mis-use of Access is.

[boykster]

Depending on how loose of a definition you use for "database", then sure Excel is one, but it's definately not a database engine. 

You could apply that logic to a pen and a piece of paper, too.  At some point you have to draw a line between a table or two and an actual working database (even if you have to call Access a working database).

Yep, totally agree. 

[AmericanDemon]

Thank you so very much for all of the help.  Its awesome that we have so many knowledgable people in our ranks.    I think we got everything sorted out, but I guess her grade will tell the truth of it.  LOL.  Once again, thanks a ton!

[patrickl]

Access isn't evil, but mis-use of Access is.

Indeed.

With Excel there is almost no other way than mis-use though. I never had much trouble upsizing (/converting) an Access database, but with Excel it's typically a nightmare to get anything out of it. They tend to have 1000 customers with each their own file that has changed slightly (and preferably at random) over the years.