Relationship not producing expected result(s)

[Lee Ann]

Hoping this is a simple fix and my initial set up is correct. I have a
number of tables in a DB which is tracking particular criminal activity. For
the most part, other crimes may be involved with the main activity, but not
always (field may need to be blank). When I try to pass that field without
entry, Access says "there's no matching record" and I have to add a crime for
it to save the record.

My table set-up is:

TblAdditionalCriminalCharges
AdditionalCriminalChargesID(PK)
AdditionalCriminalCharges1
AdditionalCriminalCharges2
AdditionalCriminalCharges3
AdditionalCriminalCharges4

This table is joined to the table which houses the information pertaining to
the subject (criminal) by use of a FK titled AdditionalCriminalChargesID.

Thanks in advance for any assistance.

 

[XPS350]

Hoping this is a simple fix and my initial set up is correct.  I have a
number of tables in a DB which is tracking particular criminal activity.  For
the most part, other crimes may be involved with the main activity, but not
always (field may need to be blank).  When I try to pass that field without
entry, Access says "there's no matching record" and I have to add a crimefor
it to save the record.  

My table set-up is:

TblAdditionalCriminalCharges
AdditionalCriminalChargesID(PK)
AdditionalCriminalCharges1
AdditionalCriminalCharges2
AdditionalCriminalCharges3
AdditionalCriminalCharges4

This table is joined to the table which houses the information pertainingto
the subject (criminal) by use of a FK titled AdditionalCriminalChargesID.

Thanks in advance for any assistance.  

Maybe the default values of the AdditionalCriminalCharges fields are
set to 0 (see tabel design).

By the way, I don't think it is a good idea to put repeating fields in
a table. Yoy better make a related table to store (any number of)
AdditionalCriminalCharges.

Groeten,

Peter
http://access.xps350.com

 

[KARL DEWEY]

This table is joined to the table which houses the information pertaining
to the subject (criminal) by use of a FK titled AdditionalCriminalChargesID.
You have the table relationship set up backwards. You need the PK of
tblSubject one-to-many of TblAdditionalCriminalCharges using a FK there,
selecting options Referential Integerity and Cascade Update.
That table like XPS350 said to look like this --
TblAdditionalCriminalCharges --
AdditionalCriminalChargesID (PK)
CriminalID (FK)
AdditionalCriminalCharges

 

[John W. Vinson]

Hoping this is a simple fix and my initial set up is correct. I have a
number of tables in a DB which is tracking particular criminal activity. For
the most part, other crimes may be involved with the main activity, but not
always (field may need to be blank). When I try to pass that field without
entry, Access says "there's no matching record" and I have to add a crime for
it to save the record.

My table set-up is:

TblAdditionalCriminalCharges
AdditionalCriminalChargesID(PK)
AdditionalCriminalCharges1
AdditionalCriminalCharges2
AdditionalCriminalCharges3
AdditionalCriminalCharges4

This table is joined to the table which houses the information pertaining to
the subject (criminal) by use of a FK titled AdditionalCriminalChargesID.

Thanks in advance for any assistance.

Then the table setup is WRONG. Any time you have fields with a sequential
number at the end, it's a red flag that you're trying to jam a one (case) to
many (additional charges) relationship into a single record.

"Fields are expensive, records are cheap". What you need is a table with one
RECORD - not one field - for each additional charge. I'm guessing that you
have a "main table" with a primary key (ActivityID, ChargeID, whatever that
might be). You would have an AdditionalCharges table with a field - called a
"foreign key" - as a link to this table, and a field for the charge. I would
expect that you would also have, somewhere in the database, a table of all the
possible charges so you could store just the ID of the charge rather than
having to type the full legalese verbiage on each charge!

 

[Lee Ann]

I am not creating 4 additional tables, correct (as I want to allow 4 other
charges to be listed)? By choosing Cascade Update, that field is being
updated whenever a record is entered? My confusion is coming in with respect
to 4 charges and not being able to picture how these values are stored in one
field.

Thanks in advance for your help.

 

[PieterLinden via AccessMonster.com]

I am not creating 4 additional tables, correct (as I want to allow 4 other
charges to be listed)? By choosing Cascade Update, that field is being
updated whenever a record is entered? My confusion is coming in with respect
to 4 charges and not being able to picture how these values are stored in one
field.

Think of a main form, say "Crime" and then a subform, "Charges". This way,
you can add as many charges as you need to a single Crime record.

 

[KARL DEWEY]

There would be TWO tables, tblCriminal and TblAdditionalCriminalCharges.
Yes. Data entry would use a form/subform (Criminal/Charges) with
Master/Child links set using CriminalID field of the two tables.
They are in separate records that are related to the tblCriminal table by
way of CriminalID.

 

[John W. Vinson]

I am not creating 4 additional tables, correct (as I want to allow 4 other
charges to be listed)?

Only one additional table: tblAdditionalCharges, with a field CriminalID (or
whatever the link to the parent table might be) and a field for the charge.

By choosing Cascade Update, that field is being
updated whenever a record is entered?

No. Cascade Update has nothing to do with it, and in fact it should not be set
at all!

If you're entering data in table datasheets - DON'T. That's not what they're
for!

Instead, you would use a Form (for the Criminal, or the Case, or the
MainCharge, whatever is the parent table in your case) with a Subform for the
additional charges. If you use a continuous Subform you can see zero, or one,
or six, or seventeen rows for the additional charges; and the Subform's
Master/Child Link Field feature will fill in the linking field when you add a
new record.

My confusion is coming in with respect
to 4 charges and not being able to picture how these values are stored in one
field.

You would fill in four *ROWS* - not four fields, not four textboxes - in the
AdditionalCharges table. If the linking field is CriminalID, and you're
entering the case of "Tom Peeper", CriminalID 123, you would have four rows
like

123 "Trespassing"
123 "Mopery with Intent to Gawk"
123 "Eluding"
123 "Looking Funny at a Cop"

or (better) four rows each with a link to a table of Charges.