Solved

relationship table

Posted on 2002-04-18
5
285 Views
Last Modified: 2012-08-14
hi everyone new member

I'm cleaning up a database built before me and have a table called
discovery the table looks like this


    (primary key)   case
                    plaintiff deposition
                    vehicle inspection
                    plaintiff expert(1)
                    plaintiff expert(2)
                    plaintiff expert(3)
                    plaintiff expert(4)
                    co-def 1
                    co-def1-expert(1)
                    co-def1-expert(2)
                    co-def1-expert(3)
                    co-def-expert(4)
                    and so on to co-def 4
                    dcc expert (1)
                    dcc expert(2)
                    dcc expert(3)
                    dcc expert(4)
                    dcc rogs on cod
                    dcc rogs on cod due
                    plaintiff expert report due
                    plaintiff expert report served
                    co-def med expert report due
                    and so on

looking at discovery relationship with case info table it has a one-to-one relationship so that means i should add it to case info but in the table sometimes thier are zero to two co-def and if thier is a co-def maybe they have just one expert is their any way to build the tables to fix this problem?
0
Comment
Question by:james121
5 Comments
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
I need more info.

Try changing the join type to type 2.

Thanks

Jell
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 50 total points
Comment Utility
Okay we need to denormalize this data.  The way I see it, you need three more separate  tables:

CoDefendent (Actually this should be just defendent.)
(CodefNumber Int,
 Case Text,
 Name Text)
Composite Primary key on CodefNumber and case

Plaintiff
(PalintiffNumber int,
 Case text,
 Name text)
Composite Primary key on PlainiffNumber and case

Expert
(ExpertNumber Integer,
 Case Text,
 Name Text,
 CoDefNumber Integer (Allow Nulls),
 PlantiffNumber Integer (Allow Nulls),
 ReportServed DateTime,
 ReportDue DateTime)
Composite Primary key on ExpertNumberNumber and case

In each of these tables multiples are allowed per case. Now in your case table you get rid of Paintiff, Expert and Codefendent related fields.  And a query with a simple join will return all the info you need.
0
 
LVL 5

Expert Comment

by:spcmnspff
Comment Utility
Sorry I meant to say "we need to normalize this data", it's already denormalized. =)  Anyway the recomendations are good. . .
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
for james121

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: spcmnspff  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
Comment Utility
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now