There would be TWO tables, tblCriminal and TblAdditionalCriminalCharges.

By choosing Cascade Update, that field is being updated whenever a record is entered?
Yes. Data entry would use a form/subform (Criminal/Charges) with
Master/Child links set using CriminalID field of the two tables.

My confusion is coming in with respect to 4 charges and not being able to picture how these values are stored in one field.
They are in separate records that are related to the tblCriminal table by
way of CriminalID.


--
Build a little, test a little.


"Lee Ann" wrote:

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.

"John W. Vinson" wrote:

On Thu, 4 Feb 2010 08:18:03 -0800, Lee Ann
wrote:

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!
--

John W. Vinson [MVP]
.