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
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi folks,
I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#2
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
my first thought is: do you really need to to separate the individuals
records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#3
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Thanks for your interest Tina,
My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#4
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
hmm, okay. i don't know that i'd set up the tables quite that way, but
you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#5
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
btw, suggest you use one subform in your main form. when the user chooses an
option in the main form's combo box control, then use VBA to set the subform's SourceObject, LinkChildFields, and LinkMasterFields properties for the appropriate subform object. going from memory, you may need to put a [SubformControlName].Form.Requery command at the end of the code, so that the correct records will populate the chosen subform. hth "tina" wrote in message ... hmm, okay. i don't know that i'd set up the tables quite that way, but you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#6
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Thanks Tina,
I'll give that go. Regards Bob "tina" wrote in message ... btw, suggest you use one subform in your main form. when the user chooses an option in the main form's combo box control, then use VBA to set the subform's SourceObject, LinkChildFields, and LinkMasterFields properties for the appropriate subform object. going from memory, you may need to put a [SubformControlName].Form.Requery command at the end of the code, so that the correct records will populate the chosen subform. hth "tina" wrote in message ... hmm, okay. i don't know that i'd set up the tables quite that way, but you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#7
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Tina,
I did have a few extra questions. Firstly, in what way might you have otherwise structured the relevant contact information? I'm a novice, so I'm open to ideas. Secondly, without complicating things too much, I should mention that my table structure includes two additional tables: tblClientFiles - a junction table with ClientID and FileID specified as the primary key; and tblFiles - which, amongst other things, has the following three fields: FileID (pk - autonumber) (with a 1:Many relationship with tblClients.FileID) FileNumber (txt - alphanumeric content (needs to be changed from time to time, so not used as pk) DateOpened - date/time DateCompleted - date/time To be honest, I struggle with insert/update commmands at the best of times. It seems even more complicated once you start normalising and multiplying the tables. Anyway, my queries a (a) based on the table structure I have outlined, how would you construct the sql strings to select, insert and delete a record into tblFiles?; and (b) how would you construct the same strings where the "client" consists of two persons (say a husband and a wife) who each have their own record in tblContacts and tblClients? By the way, someone else was pretty much asking the same question I asked at the outset (about ensuring that only one of the subtype tables was updated) - see http://www.dbforums.com/archive/inde...t-1053752.html. One of the responses to that post was as follows: Because you do not specify what is in the "generic table" [equivalent to my tblContacts], it is difficult to offer specific suggestions. I can suggest that in the generic table, you identify the type of contact [equivalent to my tblContactType], create your query linking to both, and use an outer join in order to allow one of the related tables to return a Null entry. By outer join, I mean rightclicking on the join line between the data sources in the Query, and choosing "All records from Contacts [ie my tblContacts] and only those that match from People [ie my tblIndividuals]" and "All records from Contacts and only those that match from Companies [ie my tblOrganisations]". I don't quite understand what this means. Does it mean that in order to perform select/insert/update commands programatically on, say, tblFiles or tblOrganisations, I will need to do a whole lot of "outer joins"? If so, what would one look like in my case? Regard Bob "Bob" wrote in message ... Thanks Tina, I'll give that go. Regards Bob "tina" wrote in message ... btw, suggest you use one subform in your main form. when the user chooses an option in the main form's combo box control, then use VBA to set the subform's SourceObject, LinkChildFields, and LinkMasterFields properties for the appropriate subform object. going from memory, you may need to put a [SubformControlName].Form.Requery command at the end of the code, so that the correct records will populate the chosen subform. hth "tina" wrote in message ... hmm, okay. i don't know that i'd set up the tables quite that way, but you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#8
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
comments inline.
"Bob" wrote in message ... Tina, I did have a few extra questions. Firstly, in what way might you have otherwise structured the relevant contact information? I'm a novice, so I'm open to ideas. sorry, didn't mean to be a tease. as i said, since i haven't analyzed the business process, i couldn't state an alternate tables/relationships design with confidence. i tend to stay very much "inside the box" on table/relationship design, and have trouble with the more unusual configurations that are necessary to model some real-world relationships. i wish we could get Tim Ferguson into this thread; he's about the best i've seen in these newsgroups re solving non-standard relationships and explaining the configurations in a way that is easy to understand. Secondly, without complicating things too much, I should mention that my table structure includes two additional tables: tblClientFiles - a junction table with ClientID and FileID specified as the primary key; and tblFiles - which, amongst other things, has the following three fields: FileID (pk - autonumber) (with a 1:Many relationship with tblClients.FileID) FileNumber (txt - alphanumeric content (needs to be changed from time to time, so not used as pk) DateOpened - date/time DateCompleted - date/time hmm, i do have to wonder here about your relationships. if tblFiles is related to tblClients via a junction table called tblClientFiles, then why is tblFiles also directly linked to tblClients? use of a junction table essentially says that on client may be related to many files, and one file may be related to many clients - a many-to-many relationship that the junction table resolves into two one-to-many relationships. but a direct relationship says that one client may be related to many files, but each file is related to only one client - which is a straightforward one-to-many relationship. so we have an apparent contradiction here. To be honest, I struggle with insert/update commmands at the best of times. It seems even more complicated once you start normalising and multiplying the tables. Anyway, my queries a (a) based on the table structure I have outlined, how would you construct the sql strings to select, insert and delete a record into tblFiles?; and (b) how would you construct the same strings where the "client" consists of two persons (say a husband and a wife) who each have their own record in tblContacts and tblClients? By the way, someone else was pretty much asking the same question I asked at the outset (about ensuring that only one of the subtype tables was updated) - see http://www.dbforums.com/archive/inde...t-1053752.html. One of the responses to that post was as follows: Because you do not specify what is in the "generic table" [equivalent to my tblContacts], it is difficult to offer specific suggestions. I can suggest that in the generic table, you identify the type of contact [equivalent to my tblContactType], create your query linking to both, and use an outer join in order to allow one of the related tables to return a Null entry. By outer join, I mean rightclicking on the join line between the data sources in the Query, and choosing "All records from Contacts [ie my tblContacts] and only those that match from People [ie my tblIndividuals]" and "All records from Contacts and only those that match from Companies [ie my tblOrganisations]". I don't quite understand what this means. Does it mean that in order to perform select/insert/update commands programatically on, say, tblFiles or tblOrganisations, I will need to do a whole lot of "outer joins"? If so, what would one look like in my case? sorry, hon, the above is all way too theoretical for me; i'm very much a nuts-and-bolts person. if you can lay out a specific scenario, explaining what the setup is and what you're trying to do in this situation, i'll work on a solution with you. in any event, i can't help at all until we iron out the issue of the real-world relationship between tblFiles and tblClients. hth Regard Bob "Bob" wrote in message ... Thanks Tina, I'll give that go. Regards Bob "tina" wrote in message ... btw, suggest you use one subform in your main form. when the user chooses an option in the main form's combo box control, then use VBA to set the subform's SourceObject, LinkChildFields, and LinkMasterFields properties for the appropriate subform object. going from memory, you may need to put a [SubformControlName].Form.Requery command at the end of the code, so that the correct records will populate the chosen subform. hth "tina" wrote in message ... hmm, okay. i don't know that i'd set up the tables quite that way, but you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#9
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
PMFJI :-) In a Jet (Access) database, there is no way to enforce this sort of entity subclassing at the engine level. The only way to do that would be to have two FK fields in tblContacts - one for IndivID and one for OrgID, and have a table-level constraint (validation rule) to specify that they cannot both be Null. Using the structure you have, you can go most of the way there using a BeforeUpdate event procedure on your ContactType control. Something like this (pseudo-code): If ContactType.OldValue isn't null then lookup corresponding record in table corresponding to OldValue If record exists then Heavy warning message about changing contact type If user wishes to continue then delete old related record else cancel = True End If End If End If BTW, I think you are complicating matters by having separate (AutoNumber?) PKs in your Individuals and Organisations tables. I suggest you make ContactID the PK in both those tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
#10
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Tina,
Re the relationship between tblFiles and tblClientFiles, I think I got carried away. There is no link between tblFiles and tblClients. The tblFiles.FileID has a 1:Many relationship with tblClientFiles.FileID. Sorry about that. The real-world scenario I have is this: The database is for a small accounting firm. The firm opens one file per new matter. Each time a new file is opened, the following details are recorded about the file: (a) the file's number; (b) the date the file is opened; (c) the client's name, and various contact details (ie all known addresses, telephone numbers and email addresses) - the client can be a single private individual, or a couple; or an organisation (company/partnership/association). (d) a one or two line description of the matter - eg tax returns for 2005-2006. As the file progresses, it is usually the case that a number of contacts specifically for that file might develop (eg a specific person at the tax office). The same contact might not apply to other files. I call these file-specific third-party contacts. There will usually be a number of contacts that relate specifically to the client - eg the director of a corporate client. I call these client-specific contacts. There will also be third-party contacts with whom the firm liaises on various files - eg a particular supervisor for a specific section in the tax office. I call these generic third-party contacts. I want to do the following: (a) record each new contact as it is created - regardless of whether the contact is a client, a file specific third-party contact, a client-specific client-contact, or a generic third-party contact. Hence my tblContacts, tblIndividuals and tblOrganisations tables. (b) readily identify which contacts are clients, and by whom they were referred. Hence, my tblClients table. The obvious rule here is that an individual or organisation cannot be client unless they are already a contact. (c) create a record that links a particular client (or two clients in the case of a spousal relationship) to a specific file (obviously the same client(s) may have other files - hence why I have set up a junction table between tblClients and tblFiles). (d) record the required file details. Hence my tblFiles table. (e) identify the client-specific contacts (hence my tblOrgContacts table). The same contact can be a contact for more than one client (eg where a person is the director of multiple companies). My ultimate intention is to have this information displayed to the user on a form each time they open a record in tblClients or tblFiles. (f) finally, identify the file-specific and generic third-party contacts. This information will also be displayed on a form each time a record in tblFiles is opened. My immediate aim is to ensure that anyone who wants to lookup the contact details for a particular File or Client can do so by simply opening a form linked to the relevant table and have all of the information readily displayed for viewing, updating or deleting. Now, when I create (insert) a record for a new File, what I **think** I need to do is this: (i) check if there is already a tblContact record for the Client - if not, create one. Obviously, if a new contact record is created, I'm going to need to grab the latest ContactID. Also, inserting a new record would obviously involve: (a) ensuring that there isn't already a record in either of the "subtype" tables (Individuals or Organisations); and (b) creating records in tblOrgContacts if necessary. (ii) check if the Contact has already been identified as a client - if not, create a record in tblClients; (iii) create the new record in tblFiles; and (iv) (finally?) create the new record(s) in tblClientFiles. I assume that the update and delete processes will need to traverse roughly the same procedure. What I'm not clear on is: (1) do I need a whole bunch of joins every time I insert and/or update and/or delete a record in tblFiles (say)?; and (2) how do I construct, say, an insert sql statement (for a new file) that involves so many tables? Do I need to nest a whole bunch of select statements somewhere; if so, how? Thanks Bob tina wrote: comments inline. "Bob" wrote in message ... Tina, I did have a few extra questions. Firstly, in what way might you have otherwise structured the relevant contact information? I'm a novice, so I'm open to ideas. sorry, didn't mean to be a tease. as i said, since i haven't analyzed the business process, i couldn't state an alternate tables/relationships design with confidence. i tend to stay very much "inside the box" on table/relationship design, and have trouble with the more unusual configurations that are necessary to model some real-world relationships. i wish we could get Tim Ferguson into this thread; he's about the best i've seen in these newsgroups re solving non-standard relationships and explaining the configurations in a way that is easy to understand. Secondly, without complicating things too much, I should mention that my table structure includes two additional tables: tblClientFiles - a junction table with ClientID and FileID specified as the primary key; and tblFiles - which, amongst other things, has the following three fields: FileID (pk - autonumber) (with a 1:Many relationship with tblClients.FileID) FileNumber (txt - alphanumeric content (needs to be changed from time to time, so not used as pk) DateOpened - date/time DateCompleted - date/time hmm, i do have to wonder here about your relationships. if tblFiles is related to tblClients via a junction table called tblClientFiles, then why is tblFiles also directly linked to tblClients? use of a junction table essentially says that on client may be related to many files, and one file may be related to many clients - a many-to-many relationship that the junction table resolves into two one-to-many relationships. but a direct relationship says that one client may be related to many files, but each file is related to only one client - which is a straightforward one-to-many relationship. so we have an apparent contradiction here. To be honest, I struggle with insert/update commmands at the best of times. It seems even more complicated once you start normalising and multiplying the tables. Anyway, my queries a (a) based on the table structure I have outlined, how would you construct the sql strings to select, insert and delete a record into tblFiles?; and (b) how would you construct the same strings where the "client" consists of two persons (say a husband and a wife) who each have their own record in tblContacts and tblClients? By the way, someone else was pretty much asking the same question I asked at the outset (about ensuring that only one of the subtype tables was updated) - see http://www.dbforums.com/archive/inde...t-1053752.html. One of the responses to that post was as follows: Because you do not specify what is in the "generic table" [equivalent to my tblContacts], it is difficult to offer specific suggestions. I can suggest that in the generic table, you identify the type of contact [equivalent to my tblContactType], create your query linking to both, and use an outer join in order to allow one of the related tables to return a Null entry. By outer join, I mean rightclicking on the join line between the data sources in the Query, and choosing "All records from Contacts [ie my tblContacts] and only those that match from People [ie my tblIndividuals]" and "All records from Contacts and only those that match from Companies [ie my tblOrganisations]". I don't quite understand what this means. Does it mean that in order to perform select/insert/update commands programatically on, say, tblFiles or tblOrganisations, I will need to do a whole lot of "outer joins"? If so, what would one look like in my case? sorry, hon, the above is all way too theoretical for me; i'm very much a nuts-and-bolts person. if you can lay out a specific scenario, explaining what the setup is and what you're trying to do in this situation, i'll work on a solution with you. in any event, i can't help at all until we iron out the issue of the real-world relationship between tblFiles and tblClients. hth Regard Bob "Bob" wrote in message ... Thanks Tina, I'll give that go. Regards Bob "tina" wrote in message ... btw, suggest you use one subform in your main form. when the user chooses an option in the main form's combo box control, then use VBA to set the subform's SourceObject, LinkChildFields, and LinkMasterFields properties for the appropriate subform object. going from memory, you may need to put a [SubformControlName].Form.Requery command at the end of the code, so that the correct records will populate the chosen subform. hth "tina" wrote in message ... hmm, okay. i don't know that i'd set up the tables quite that way, but you've analyzed the business process and i haven't - so i also don't know that any alternate suggestions i made would actually be "better", rather than simply different, or even not as good. so to get back to your original question: AFAIK, table constraints are user in SQL server, and perhaps other database types; but are not available in Access. so you'll need to enforce the business rule at the form level. in the main form, you can set up some code on the combo box control's BeforeUpdate event to check the "other" subtype table for a record containing the current Contact record's primary key value (a simple DCount() function would handle that easily). if it exists, you can either cancel the control's BeforeUpdate event, with a message box to tell the user to delete the current subform record before adding a record to the other subform - or tell the user that if they choose the alternate value in the combo box, the record in the current subform will be deleted, and asking them to choose to continue or cancel. if they cancel, then just cancel the BeforeUpdate event; if they continue, then automatically delete the current subform record, and then switch to the other subform. hth "Bob" wrote in message ... Thanks for your interest Tina, My full table structure is as follows: tblContacts: ContactID (pk) ContactType (fk) Address1 Address2 City State PostCode PostalAddress1 PostalAddress2 PostalCity PostalState PostalPostCode Tel Fax Mob tblIndividuals: IndivID (pk) ContactID (fk) Title FirstName MiddleNames LastName Suffix EmployerID (fk) (links back to tblContacts.ContactID (1:Many)) EmpDirectPhn EmpDirectFax EmpEmail tblOrganisations: OrgID (pk) ContactID (fk) OrgName TradingName IsACompany (yes/no) ACN (Australian Company Number) ABN (Australian Business Number) Website tblContactType ContactTypeID (pk) ContactType ("Indiv" or "Org") As you can see, tblContacts lists the location (ie residential or business) addresses, postal addresses and (residential or business) telecommunication details for all contacts. The table tblIndividuals segregates the individual-specific biographical information together with the individual's work details. The EmployerID links back to the ContactID field in tblContacts because we often end up acting for employees of existing corporate clients or for muliple employees of non-client organisations. I segregate the Organisation details so that I can record details for all businesses (incorporated and unincorporated (ie sole-proprietorships, partnerships, associations, churches etc)) that simply aren't relevant to individuals. It also enables me to set up a separate table (tblOrgContacts) to identify individual contacts for the organisation entities (a 1:Many relationship is established between the two tables based on tblOrganisations.OrgID (pk) and tblOrgContacts.ContactID (fk)). For our purposes, we do not require any contacts to be linked with Individuals as opposed to Organisations. The above tables essentially constitute the whole set of "contacts" for my employer's business; tblContacts is then linked with tblClients which identifies those contacts that are in fact clients: tlbClients: ClientID (pk) (autonumber) ContactID (fk) (related to tblContacts.ContactID) (1:1 relationship) ReferrerID (fk) (related to tblContacts.ContactID) (1:Many relationship) Regards Bob "tina" wrote in message ... my first thought is: do you really need to to separate the individuals records and organizations records into different tables? suggest you post all the fields in each of those two tables so we can review them; perhaps we can help you combine the two tables into one, with the addition of a single field specifying either "individual" or "organization". hth "Bob" wrote in message ... Hi folks, I am creating a client database in MS Access with the following (simplified) table structu tblContacts: ContactID (pk - autonumber) ContactType (fk) (from tblContactTypes) ContactDetails (text) tblIndividuals IndivID (pk - autonumber) ContactID (fk) (from tblContacts) IndivDetails (txt) tblOrganisations OrgID (pk - autonumber) ContactID (fk) (from tblContacts) OrgDetails (txt) tblContactTypes (serves as a lookup table) tblContactTypeID (pk - autonumber) tblContactType (txt - contains values "Indiv" or "Org") There is a 1:1 relationship between the ContactID (pk) in tblContacts and the ContactID (fks) in tblIndividuals and tbleOrganisations. I have created a form in MS Access for entering client details. At the moment, I have two subforms - frmIndiv and frmOrg - which are positioned on my main entry form. The form contains a combo-box from which the user can select "Indiv" or "Org" as the ContactType. Depending on the value in the combo-box, one or other of the two subforms will become visible. At the moment, the user selects - say - "Indiv" as the ContactType and proceeds to enter details for this type of Contact. When this happens, the ContactID for the current record in tblContacts table is mirrored in the ContactID foreign key in the tblIndividuals table. This is what I want. The problem is that once the user is finished (and whilst still in the same record in the tblContacts table), the user can select "Org" from the combo-box and be provided with a empty copy of the sub-form frmOrg. If the user proceeds to enter data on the sub-form, the ContactID foreign key in the frmOrg will also mirror the ContactID in tblContacts. This results in a record in both of my subtype tables (tblIndividuals and tblOrganisations) having a record which points to the same ContactID in the supertype table (tblContacts). How can I prevent this from happening? - ie make sure that each record in the subtype tables points to a record in the supertype table for which no subtype record has already been created? (That's a mouthful - I hope it makes sense). I've seen some references to "check constraints" on the internet which I believe might help achieve my objective. But - so far as I am aware - I can't impose check constraints on fields in Access 2000. (I have seen a suggestion that this might be achieved by using ADO, but no code example was given). Any pointers would be appreciated. Please note, I am a complete novice at this. TIA Bob |
Thread Tools | |
Display Modes | |
|
|