Prev: Access 2007 MSACCESS.EXE consuming RAM
Next: Print reports with creteria - 2 questions..
From: Lee Ann on 4 Feb 2010 11:18
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.
From: XPS350 on 4 Feb 2010 11:39
On 4 feb, 17:18, Lee Ann <Lee...(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.  

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
From: KARL DEWEY on 4 Feb 2010 13:19
>>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

--
Build a little, test a little.


"XPS350" wrote:

> On 4 feb, 17:18, Lee Ann <Lee...(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.
>
> 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
> .
>
From: John W. Vinson on 4 Feb 2010 13:35
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: Lee Ann on 4 Feb 2010 15:29
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]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Access 2007 MSACCESS.EXE consuming RAM
Next: Print reports with creteria - 2 questions..