Relationship Help

  • Thread starter Thread starter lettyg82
  • Start date Start date
L

lettyg82

I have uploaded a screen shot of my relationships window for the database I
have designed. I believe what i want is a one to many relationship. The
Parent Table is FCCaseInfo and its primary key is CaseIdNo. The child tables
should be FVVW, FVCharges, and FVDates these tables also have the CaseIdNo
(not primary, but i added this field to somehow relate all of these
together).

Any suggestions as to what my relationships should be?

Thanks!

http://cid-af7c698d9d142706.skydrive.live.com/self.aspx/.Public/Database.JPG
 
I have uploaded a screen shot of my relationships window for the database I
have designed. I believe what i want is a one to many relationship. The
Parent Table is FCCaseInfo and its primary key is CaseIdNo. The child tables
should be FVVW, FVCharges, and FVDates these tables also have the CaseIdNo
(not primary, but i added this field to somehow relate all of these
together).

Any suggestions as to what my relationships should be?

Thanks!

http://cid-af7c698d9d142706.skydrive.live.com/self.aspx/.Public/Database.JPG
Click to expand...

Without more understanding of the nature of the data you're managing, I can't
comfortably say what the relationships "should" be... but what specific
problem are you having? Can you create a link from FCCaseInfo's CaseIDNo
primary key to the CaseIdNo in the FVVW child table? If not, what's causing
the problem?

Are you perhaps expecting that FVVW will automagically create a new record for
each CaseIDNo? If so, it won't: you will only want to create a record when you
have data to put into the child table. The normal way to manage this is to use
a Form based on the parent table, with a Subform (or subforms) based on the
child table(s), using CaseIDNo as the master/child linking field.
 
FVCaseInfo = main (parent table - CaseIdNo is the primary key)
FVCharges = child table
FVVW = child table
FVDates = child table

An FVCaseInfo record will be created per defendant/docket number, each case
will have a charges record with count number (there can be more than one
count per case info record), charge code, crime name, statue, disposition per
count, sentencing per count .

This database keeps track of our Felony Domestic Violence Cases. I have four
tables that are associated with CaseIdNo. All the tables that are look up
tables are labeled LU.

Should I have the FVCaseInfo (CaseIdNo=Primary Key) table as One to many
table linked to:

FVCharges by (caseIdNo). The FVCharges Table has the counts per case,
statue,
sentencing etc.

and

FVVW table by CaseIdNo. (FVVW has the times the victim was contacted by a
victim witness specialist - this also has other stats I am keeping track of
from that specific unit). There is one FVVW Record for each case (only one
record is required per
case).

and

FVDates table I want linked to the FVCaseInfo table by CaseIdNo.
There will be one set of dates per case. (Arrest Date, Preliminary Hearing,
Arraignemnt Date, Sentencing Date, etc.)

and

I want to make sure i create the right relationship and join type. I know
my Case
Information table should be a parent table and the FVcharges table, FVVW
table and FVDates table should be children tables. I haven't joined them yet
because i want to
choose the right join type. There should not be any records created for
FVCharges, FVVW, or FVDates without first being an FVCaseInfo record created.
 
I want to make sure i create the right relationship and join type. I know
my Case
Information table should be a parent table and the FVcharges table, FVVW
table and FVDates table should be children tables. I haven't joined them yet
because i want to
choose the right join type. There should not be any records created for
FVCharges, FVVW, or FVDates without first being an FVCaseInfo record created.
Click to expand...

The foreign key fields in the three tables should be of the same datatype as
the case ID in the main table (Long Integer if the primary key is an
Autonumber). Join the tables by that field, and enforce referential integrity.
Check the "Enforce Referential Integrity" checkbox. It's not necessary to do
anything with the Cascade Update or Cascade Delete checkboxes (in fact, if
it's an autonumber the Update is irrelevant since you can't edit an autonumber
value, and Cascade Deletes is generally considered dangerous since it can lead
to ALL data for a case being deleted with one click!)

Access will automatically define the relationship as one to many, since you're
linking from a unique primary key ("one") to a non-unique foreign key
("many"). The program will automatically create indexes on the foreign key
fields, so don't do so yourself.

You're on the right track!
 
As I indicated in response to your other duplicate post of this quesiton

IMHO, if you put the CaseID number into those three "child tables" are are
able to put a single CaseID number into that field in each of those child
records, the you have already essentially created and told us about the
relationships, determined that they are not many-to-many, and all that
remains is to "draw the lines" between the CaseID field to the field of that
name in each of the child tables. You'll still have some secondary choices
to make (referential integrity and which, if any, unmatched records that you
so (which will presumably be those of your parent table)
 
Back
Top