A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relationship not producing expected result(s)



 
 
Thread Tools Display Modes
  #1  
Old February 4th, 2010, 05:18 PM posted to microsoft.public.access.tablesdbdesign
Lee Ann[_2_]
external usenet poster
 
Posts: 38
Default Relationship not producing expected result(s)

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.
  #2  
Old February 4th, 2010, 05:39 PM posted to microsoft.public.access.tablesdbdesign
XPS350
external usenet poster
 
Posts: 69
Default Relationship not producing expected result(s)

On 4 feb, 17:18, 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. *


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
  #3  
Old February 4th, 2010, 07:19 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Relationship not producing expected result(s)

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

  #4  
Old February 4th, 2010, 07:35 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Relationship not producing expected result(s)

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]
  #5  
Old February 4th, 2010, 09:29 PM posted to microsoft.public.access.tablesdbdesign
Lee Ann[_2_]
external usenet poster
 
Posts: 38
Default Relationship not producing expected result(s)

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

  #6  
Old February 4th, 2010, 10:05 PM posted to microsoft.public.access.tablesdbdesign
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default Relationship not producing expected result(s)

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/For...esign/201002/1

  #7  
Old February 4th, 2010, 10:18 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Relationship not producing expected result(s)

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

  #8  
Old February 5th, 2010, 12:36 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Relationship not producing expected result(s)

On Thu, 4 Feb 2010 12:29:01 -0800, Lee Ann
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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.