Solved

relationship table

Posted on 2002-04-18
5
289 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
[X]
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
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

749 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