Prev: Access 2007 MSACCESS.EXE consuming RAM
Next: Print reports with creteria - 2 questions..
From: PieterLinden via AccessMonster.com on 4 Feb 2010 16:05
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.
>
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201002/1

From: KARL DEWEY on 4 Feb 2010 16:18
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 <LeeAnn(a)discussions.microsoft.com>
> > 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]
> > .
> >
From: John W. Vinson on 4 Feb 2010 18:36
On Thu, 4 Feb 2010 12:29:01 -0800, Lee Ann <LeeAnn(a)discussions.microsoft.com>
wrote:

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

John W. Vinson [MVP]
First  |  Prev  | 
Pages: 1 2
Prev: Access 2007 MSACCESS.EXE consuming RAM
Next: Print reports with creteria - 2 questions..