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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|