Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

relationship table

Posted on 2002-04-18
5
288 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
ID: 6951127
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
ID: 6951249
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
ID: 6951252
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
ID: 7119828
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
ID: 7134582
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

856 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