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
|
|||
|
|||
Unable to edit records in a form or query
I have created a form where the data is generated from a select query. I can
edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
#2
|
|||
|
|||
You will need to provide more information. Please describe what the database
is for, the contents of the two tables (key fields and anything relevant to the question, but it is not necessary to list every field), and the relationships. Have you established primary keys in the table? You're not trying to establish a relationship between two primary key fields, are you? Just guessing. "Merlin" wrote: I have created a form where the data is generated from a select query. I can edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
#3
|
|||
|
|||
Bruce,
Thanks for the reply. I will do my best to describe it for you. I am tracking resources for multiple projects. My primary table is "PROJECT" it contains all project information, including the project number and title. The query that i am running is pulling information form three discipline tables "CSA", "ARCHITECTURAL" and "HVAC". The common field in all of these tables is "PROJNO". The PROJNO field (data type =TEXT)in the PROJECT table is the Primary Key Field. All other tables have unique identifiers automatically generated as the Primary Key Field. For instance, in the CSA table the key field is "CSAIND" (data type = autonumber). I hope that this makes since. "BruceM" wrote: You will need to provide more information. Please describe what the database is for, the contents of the two tables (key fields and anything relevant to the question, but it is not necessary to list every field), and the relationships. Have you established primary keys in the table? You're not trying to establish a relationship between two primary key fields, are you? Just guessing. "Merlin" wrote: I have created a form where the data is generated from a select query. I can edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
#4
|
|||
|
|||
A primary key (PK) is a unique identifier, no matter the data type.
Autonumber is one way of creating a unique identifier, that's all. Is there a relationship between PROJNO in the Projects table and PROJNO in the other tables? To put it another way, does each record in, say, the CSA table relate to a specific record in the Projects table, or can each record in the CSA table relate to many records in the Projects table. Is the CSA table fairly static, or are records created on the fly? By way of example, an Employees table is usually fairly static. If you are entering payroll information you first need to create an employee record, but payroll information is entered as you go, with a new entry (record) each week. I understand a bit more about your database, but still do not understand the relationships between the tables. A relationship is something you have to deliberately create, so it is not a question of relationships being set up without your knowledge. What relationships did you create? "Merlin" wrote: Bruce, Thanks for the reply. I will do my best to describe it for you. I am tracking resources for multiple projects. My primary table is "PROJECT" it contains all project information, including the project number and title. The query that i am running is pulling information form three discipline tables "CSA", "ARCHITECTURAL" and "HVAC". The common field in all of these tables is "PROJNO". The PROJNO field (data type =TEXT)in the PROJECT table is the Primary Key Field. All other tables have unique identifiers automatically generated as the Primary Key Field. For instance, in the CSA table the key field is "CSAIND" (data type = autonumber). I hope that this makes since. "BruceM" wrote: You will need to provide more information. Please describe what the database is for, the contents of the two tables (key fields and anything relevant to the question, but it is not necessary to list every field), and the relationships. Have you established primary keys in the table? You're not trying to establish a relationship between two primary key fields, are you? Just guessing. "Merlin" wrote: I have created a form where the data is generated from a select query. I can edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
#5
|
|||
|
|||
Bruce,
The Project table has the PROJNO field in it. The relationship in my query is to tie this field to the PROJNO field in the other tables (CSA, ARCHITECTURAL, HVAC). The CSA table for instance has PROJNO, CSAbudget, CSAexpend, CSAETC, CSAIFC...) This data is specific to the discipline CSA. The only field in the CSA table that relates to the Project table is PROJNO. CSA table has many records. Not all project will have CSA budgets and so forth, but many do. In my form, the query should generate the current data for each project and then I should be allowed to change certain fields, like if it is active or not, who is the engineer. These fields are in the CSA table but they are blank until I enter them manually, because they can change. i hope that this helps. Thanks for your help on this. I have spent days trying everything that I can think of with no luck. Sincerely, Merlin "BruceM" wrote: A primary key (PK) is a unique identifier, no matter the data type. Autonumber is one way of creating a unique identifier, that's all. Is there a relationship between PROJNO in the Projects table and PROJNO in the other tables? To put it another way, does each record in, say, the CSA table relate to a specific record in the Projects table, or can each record in the CSA table relate to many records in the Projects table. Is the CSA table fairly static, or are records created on the fly? By way of example, an Employees table is usually fairly static. If you are entering payroll information you first need to create an employee record, but payroll information is entered as you go, with a new entry (record) each week. I understand a bit more about your database, but still do not understand the relationships between the tables. A relationship is something you have to deliberately create, so it is not a question of relationships being set up without your knowledge. What relationships did you create? "Merlin" wrote: Bruce, Thanks for the reply. I will do my best to describe it for you. I am tracking resources for multiple projects. My primary table is "PROJECT" it contains all project information, including the project number and title. The query that i am running is pulling information form three discipline tables "CSA", "ARCHITECTURAL" and "HVAC". The common field in all of these tables is "PROJNO". The PROJNO field (data type =TEXT)in the PROJECT table is the Primary Key Field. All other tables have unique identifiers automatically generated as the Primary Key Field. For instance, in the CSA table the key field is "CSAIND" (data type = autonumber). I hope that this makes since. "BruceM" wrote: You will need to provide more information. Please describe what the database is for, the contents of the two tables (key fields and anything relevant to the question, but it is not necessary to list every field), and the relationships. Have you established primary keys in the table? You're not trying to establish a relationship between two primary key fields, are you? Just guessing. "Merlin" wrote: I have created a form where the data is generated from a select query. I can edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
#6
|
|||
|
|||
I don't know what CSA is, but I know what HVAC is, so I will use that as an
example of how a relationship between PROJNO in the projects table and PROJNO in the HVAC table would work. Each project contains many cost items. For HVAC the first record may be blowers (type, size, brand, etc.), the second item may be ducts, the third item diffusers, and so forth. In other words, each job would contain an itemized listing of HVAC expenses. If PROJNO in the Projects table is 101, and if a one-to-many relationship is established between Projects and HVAC, then when you enter a record in Projects you also have the opportunity to enter related records in HVAC. You can see this in the table (Access 2000 and above, I think) by the presence of a + sign next to each record in Projects. Clicking the + sign will show you the fields in HVAC. In actual use you would do this with a form and subform. You would make a form based on Projects and another on HVAC. In form design view drag the icon for the HVAC form onto the Projects form. When you enter a record in the Projects table (via the form) you can enter related records (as many as you need) in the HVAC table (via the subform). Each record created in this way in the HVAC table will relate to one and only one record in the Projects table. If on the other hand the HVAC table contains options from which you must choose, and if those options are available to other Projects as well, that is another matter. For instance, if the HVAC table contains a listing of components available to all projects, you do not want a one-to-many relationship between the Projects table and the HVAC table. Or there could be a third option, in which you select the item as described in the previous paragraph, then enter quantity and current pricing, or something like that. In either the second or third option you would need a separate table (let's call it tblProjHVAC) to store either the primary key from the HVAC table, or the actual values if they are subject to change (price information, for instance). One point here is that the CSA, ARCHITECTURAL, and HVAC tables (or tblProjHVAC, tblProjCSA, and tblProjArch) all relate separately to the Projects table, but do not relate to each other. For your purposes there is no need to tie them together in a query. You may want to use queries for other reasons such as sorting or criteria, but that is another matter. "Merlin" wrote: Bruce, The Project table has the PROJNO field in it. The relationship in my query is to tie this field to the PROJNO field in the other tables (CSA, ARCHITECTURAL, HVAC). The CSA table for instance has PROJNO, CSAbudget, CSAexpend, CSAETC, CSAIFC...) This data is specific to the discipline CSA. The only field in the CSA table that relates to the Project table is PROJNO. CSA table has many records. Not all project will have CSA budgets and so forth, but many do. In my form, the query should generate the current data for each project and then I should be allowed to change certain fields, like if it is active or not, who is the engineer. These fields are in the CSA table but they are blank until I enter them manually, because they can change. i hope that this helps. Thanks for your help on this. I have spent days trying everything that I can think of with no luck. Sincerely, Merlin "BruceM" wrote: A primary key (PK) is a unique identifier, no matter the data type. Autonumber is one way of creating a unique identifier, that's all. Is there a relationship between PROJNO in the Projects table and PROJNO in the other tables? To put it another way, does each record in, say, the CSA table relate to a specific record in the Projects table, or can each record in the CSA table relate to many records in the Projects table. Is the CSA table fairly static, or are records created on the fly? By way of example, an Employees table is usually fairly static. If you are entering payroll information you first need to create an employee record, but payroll information is entered as you go, with a new entry (record) each week. I understand a bit more about your database, but still do not understand the relationships between the tables. A relationship is something you have to deliberately create, so it is not a question of relationships being set up without your knowledge. What relationships did you create? "Merlin" wrote: Bruce, Thanks for the reply. I will do my best to describe it for you. I am tracking resources for multiple projects. My primary table is "PROJECT" it contains all project information, including the project number and title. The query that i am running is pulling information form three discipline tables "CSA", "ARCHITECTURAL" and "HVAC". The common field in all of these tables is "PROJNO". The PROJNO field (data type =TEXT)in the PROJECT table is the Primary Key Field. All other tables have unique identifiers automatically generated as the Primary Key Field. For instance, in the CSA table the key field is "CSAIND" (data type = autonumber). I hope that this makes since. "BruceM" wrote: You will need to provide more information. Please describe what the database is for, the contents of the two tables (key fields and anything relevant to the question, but it is not necessary to list every field), and the relationships. Have you established primary keys in the table? You're not trying to establish a relationship between two primary key fields, are you? Just guessing. "Merlin" wrote: I have created a form where the data is generated from a select query. I can edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
#7
|
|||
|
|||
Bruce,
I am copying the SQL for the Query and a table so that you may understand my delima better. The form will show the all the information from the query. I will want to edit the following cells: CSACOMMENTS, CSAIFC, CSAPROJCD,CSAENG and CSADES. All other fields are generated from accounting and are manhours budgeted and spent and estimated to complete. These should not be edited. My Query: SELECT DISTINCTROW CSA.CSAPROJCD, CSA.PROJNO, CSA.CSAENG, CSA.CSADES, CSA.CSACOMMENTS, CSA.CSABUD, CSA.CSAETC, ARCHITECTURE.ARCHBUD, ARCHITECTURE.ARCHETC, PROJECT.PROJTITLE, PROJECT.PROJMGR, PROJCLIENT.CLIENTNAME, PROJCLIENT.PROJCLIENTMGR, CSA.CSAIFC, HVAC.HVACBUD, HVAC.HVACETC FROM (((PROJECT INNER JOIN CSA ON PROJECT.PROJNO = CSA.PROJNO) INNER JOIN PROJCLIENT ON PROJECT.PROJNO = PROJCLIENT.PROJNO) INNER JOIN ARCHITECTURE ON PROJECT.PROJNO = ARCHITECTURE.PROJNO) INNER JOIN HVAC ON PROJECT.PROJNO = HVAC.PROJNO WHERE (((CSA.CSAPROJCD)"X" Or (CSA.CSAPROJCD) Is Null) AND ((CSA.CSAETC)0)) OR (((CSA.CSAPROJCD)"X" Or (CSA.CSAPROJCD) Is Null) AND ((ARCHITECTURE.ARCHETC)0)) OR (((CSA.CSAPROJCD)"X") AND ((CSA.CSAIFC) Is Not Null)) OR (((CSA.CSAPROJCD)"X") AND ((CSA.CSAENG) Is Not Null)) OR (((CSA.CSAPROJCD)"X" Or (CSA.CSAPROJCD) Is Null) AND ((HVAC.HVACETC)0)) ORDER BY CSA.CSAPROJCD, CSA.PROJNO; CSA Table: CSAIND.....Autonumber.....long Integer PROJO......Text.................Indexed(duplicates ok) CSAPROJCD..text..............Indexed (Duplicates ok) CSAENG.......text.............. CSADES.......text CSACHKR.....text CSAISSUE....date/time CSADWGTYP..text CSACOMMENTS...text CSAIFC.........date/time CSABUD.......number CSASTD.......number CSAETC.......number CSAINDF......number CSAPER.......number I hope that this will shed some light on what is going on. "BruceM" wrote: I don't know what CSA is, but I know what HVAC is, so I will use that as an example of how a relationship between PROJNO in the projects table and PROJNO in the HVAC table would work. Each project contains many cost items. For HVAC the first record may be blowers (type, size, brand, etc.), the second item may be ducts, the third item diffusers, and so forth. In other words, each job would contain an itemized listing of HVAC expenses. If PROJNO in the Projects table is 101, and if a one-to-many relationship is established between Projects and HVAC, then when you enter a record in Projects you also have the opportunity to enter related records in HVAC. You can see this in the table (Access 2000 and above, I think) by the presence of a + sign next to each record in Projects. Clicking the + sign will show you the fields in HVAC. In actual use you would do this with a form and subform. You would make a form based on Projects and another on HVAC. In form design view drag the icon for the HVAC form onto the Projects form. When you enter a record in the Projects table (via the form) you can enter related records (as many as you need) in the HVAC table (via the subform). Each record created in this way in the HVAC table will relate to one and only one record in the Projects table. If on the other hand the HVAC table contains options from which you must choose, and if those options are available to other Projects as well, that is another matter. For instance, if the HVAC table contains a listing of components available to all projects, you do not want a one-to-many relationship between the Projects table and the HVAC table. Or there could be a third option, in which you select the item as described in the previous paragraph, then enter quantity and current pricing, or something like that. In either the second or third option you would need a separate table (let's call it tblProjHVAC) to store either the primary key from the HVAC table, or the actual values if they are subject to change (price information, for instance). One point here is that the CSA, ARCHITECTURAL, and HVAC tables (or tblProjHVAC, tblProjCSA, and tblProjArch) all relate separately to the Projects table, but do not relate to each other. For your purposes there is no need to tie them together in a query. You may want to use queries for other reasons such as sorting or criteria, but that is another matter. "Merlin" wrote: Bruce, The Project table has the PROJNO field in it. The relationship in my query is to tie this field to the PROJNO field in the other tables (CSA, ARCHITECTURAL, HVAC). The CSA table for instance has PROJNO, CSAbudget, CSAexpend, CSAETC, CSAIFC...) This data is specific to the discipline CSA. The only field in the CSA table that relates to the Project table is PROJNO. CSA table has many records. Not all project will have CSA budgets and so forth, but many do. In my form, the query should generate the current data for each project and then I should be allowed to change certain fields, like if it is active or not, who is the engineer. These fields are in the CSA table but they are blank until I enter them manually, because they can change. i hope that this helps. Thanks for your help on this. I have spent days trying everything that I can think of with no luck. Sincerely, Merlin "BruceM" wrote: A primary key (PK) is a unique identifier, no matter the data type. Autonumber is one way of creating a unique identifier, that's all. Is there a relationship between PROJNO in the Projects table and PROJNO in the other tables? To put it another way, does each record in, say, the CSA table relate to a specific record in the Projects table, or can each record in the CSA table relate to many records in the Projects table. Is the CSA table fairly static, or are records created on the fly? By way of example, an Employees table is usually fairly static. If you are entering payroll information you first need to create an employee record, but payroll information is entered as you go, with a new entry (record) each week. I understand a bit more about your database, but still do not understand the relationships between the tables. A relationship is something you have to deliberately create, so it is not a question of relationships being set up without your knowledge. What relationships did you create? "Merlin" wrote: Bruce, Thanks for the reply. I will do my best to describe it for you. I am tracking resources for multiple projects. My primary table is "PROJECT" it contains all project information, including the project number and title. The query that i am running is pulling information form three discipline tables "CSA", "ARCHITECTURAL" and "HVAC". The common field in all of these tables is "PROJNO". The PROJNO field (data type =TEXT)in the PROJECT table is the Primary Key Field. All other tables have unique identifiers automatically generated as the Primary Key Field. For instance, in the CSA table the key field is "CSAIND" (data type = autonumber). I hope that this makes since. "BruceM" wrote: You will need to provide more information. Please describe what the database is for, the contents of the two tables (key fields and anything relevant to the question, but it is not necessary to list every field), and the relationships. Have you established primary keys in the table? You're not trying to establish a relationship between two primary key fields, are you? Just guessing. "Merlin" wrote: I have created a form where the data is generated from a select query. I can edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
#8
|
|||
|
|||
I'm afraid you are going to have to start a new thread. I am not skilled at
evaluating SQL statements, which is why I asked about the structure and purpose of the database. I can see where CSA, HVAC, and ARCHITECTURE could each relate to the Projects table, but I don't understand why they need to be combined into a query, nor how they relate to each other. I still am inclined to guess that you need PROJNO as the foreign key in each of those three tables, with a one-to-many relationship between PROJNO in the Projects table and PROJNO in each of the other three tables. If you need to sum the costs for CSA, HVAC, and ARCHITECTURAL for each project there are probably better ways than a query of doing that. There are plenty of people here who are familiar with SQL scripts, and perhaps all of what I have asked is answered there. I outlined several possible scenarios in my previous post. It appears that none are relevant, but I don't know with what to replace them. Sorry I couldn't be more help. "Merlin" wrote: Bruce, I am copying the SQL for the Query and a table so that you may understand my delima better. The form will show the all the information from the query. I will want to edit the following cells: CSACOMMENTS, CSAIFC, CSAPROJCD,CSAENG and CSADES. All other fields are generated from accounting and are manhours budgeted and spent and estimated to complete. These should not be edited. My Query: SELECT DISTINCTROW CSA.CSAPROJCD, CSA.PROJNO, CSA.CSAENG, CSA.CSADES, CSA.CSACOMMENTS, CSA.CSABUD, CSA.CSAETC, ARCHITECTURE.ARCHBUD, ARCHITECTURE.ARCHETC, PROJECT.PROJTITLE, PROJECT.PROJMGR, PROJCLIENT.CLIENTNAME, PROJCLIENT.PROJCLIENTMGR, CSA.CSAIFC, HVAC.HVACBUD, HVAC.HVACETC FROM (((PROJECT INNER JOIN CSA ON PROJECT.PROJNO = CSA.PROJNO) INNER JOIN PROJCLIENT ON PROJECT.PROJNO = PROJCLIENT.PROJNO) INNER JOIN ARCHITECTURE ON PROJECT.PROJNO = ARCHITECTURE.PROJNO) INNER JOIN HVAC ON PROJECT.PROJNO = HVAC.PROJNO WHERE (((CSA.CSAPROJCD)"X" Or (CSA.CSAPROJCD) Is Null) AND ((CSA.CSAETC)0)) OR (((CSA.CSAPROJCD)"X" Or (CSA.CSAPROJCD) Is Null) AND ((ARCHITECTURE.ARCHETC)0)) OR (((CSA.CSAPROJCD)"X") AND ((CSA.CSAIFC) Is Not Null)) OR (((CSA.CSAPROJCD)"X") AND ((CSA.CSAENG) Is Not Null)) OR (((CSA.CSAPROJCD)"X" Or (CSA.CSAPROJCD) Is Null) AND ((HVAC.HVACETC)0)) ORDER BY CSA.CSAPROJCD, CSA.PROJNO; CSA Table: CSAIND.....Autonumber.....long Integer PROJO......Text.................Indexed(duplicates ok) CSAPROJCD..text..............Indexed (Duplicates ok) CSAENG.......text.............. CSADES.......text CSACHKR.....text CSAISSUE....date/time CSADWGTYP..text CSACOMMENTS...text CSAIFC.........date/time CSABUD.......number CSASTD.......number CSAETC.......number CSAINDF......number CSAPER.......number I hope that this will shed some light on what is going on. "BruceM" wrote: I don't know what CSA is, but I know what HVAC is, so I will use that as an example of how a relationship between PROJNO in the projects table and PROJNO in the HVAC table would work. Each project contains many cost items. For HVAC the first record may be blowers (type, size, brand, etc.), the second item may be ducts, the third item diffusers, and so forth. In other words, each job would contain an itemized listing of HVAC expenses. If PROJNO in the Projects table is 101, and if a one-to-many relationship is established between Projects and HVAC, then when you enter a record in Projects you also have the opportunity to enter related records in HVAC. You can see this in the table (Access 2000 and above, I think) by the presence of a + sign next to each record in Projects. Clicking the + sign will show you the fields in HVAC. In actual use you would do this with a form and subform. You would make a form based on Projects and another on HVAC. In form design view drag the icon for the HVAC form onto the Projects form. When you enter a record in the Projects table (via the form) you can enter related records (as many as you need) in the HVAC table (via the subform). Each record created in this way in the HVAC table will relate to one and only one record in the Projects table. If on the other hand the HVAC table contains options from which you must choose, and if those options are available to other Projects as well, that is another matter. For instance, if the HVAC table contains a listing of components available to all projects, you do not want a one-to-many relationship between the Projects table and the HVAC table. Or there could be a third option, in which you select the item as described in the previous paragraph, then enter quantity and current pricing, or something like that. In either the second or third option you would need a separate table (let's call it tblProjHVAC) to store either the primary key from the HVAC table, or the actual values if they are subject to change (price information, for instance). One point here is that the CSA, ARCHITECTURAL, and HVAC tables (or tblProjHVAC, tblProjCSA, and tblProjArch) all relate separately to the Projects table, but do not relate to each other. For your purposes there is no need to tie them together in a query. You may want to use queries for other reasons such as sorting or criteria, but that is another matter. "Merlin" wrote: Bruce, The Project table has the PROJNO field in it. The relationship in my query is to tie this field to the PROJNO field in the other tables (CSA, ARCHITECTURAL, HVAC). The CSA table for instance has PROJNO, CSAbudget, CSAexpend, CSAETC, CSAIFC...) This data is specific to the discipline CSA. The only field in the CSA table that relates to the Project table is PROJNO. CSA table has many records. Not all project will have CSA budgets and so forth, but many do. In my form, the query should generate the current data for each project and then I should be allowed to change certain fields, like if it is active or not, who is the engineer. These fields are in the CSA table but they are blank until I enter them manually, because they can change. i hope that this helps. Thanks for your help on this. I have spent days trying everything that I can think of with no luck. Sincerely, Merlin "BruceM" wrote: A primary key (PK) is a unique identifier, no matter the data type. Autonumber is one way of creating a unique identifier, that's all. Is there a relationship between PROJNO in the Projects table and PROJNO in the other tables? To put it another way, does each record in, say, the CSA table relate to a specific record in the Projects table, or can each record in the CSA table relate to many records in the Projects table. Is the CSA table fairly static, or are records created on the fly? By way of example, an Employees table is usually fairly static. If you are entering payroll information you first need to create an employee record, but payroll information is entered as you go, with a new entry (record) each week. I understand a bit more about your database, but still do not understand the relationships between the tables. A relationship is something you have to deliberately create, so it is not a question of relationships being set up without your knowledge. What relationships did you create? "Merlin" wrote: Bruce, Thanks for the reply. I will do my best to describe it for you. I am tracking resources for multiple projects. My primary table is "PROJECT" it contains all project information, including the project number and title. The query that i am running is pulling information form three discipline tables "CSA", "ARCHITECTURAL" and "HVAC". The common field in all of these tables is "PROJNO". The PROJNO field (data type =TEXT)in the PROJECT table is the Primary Key Field. All other tables have unique identifiers automatically generated as the Primary Key Field. For instance, in the CSA table the key field is "CSAIND" (data type = autonumber). I hope that this makes since. "BruceM" wrote: You will need to provide more information. Please describe what the database is for, the contents of the two tables (key fields and anything relevant to the question, but it is not necessary to list every field), and the relationships. Have you established primary keys in the table? You're not trying to establish a relationship between two primary key fields, are you? Just guessing. "Merlin" wrote: I have created a form where the data is generated from a select query. I can edit data in the tables that the query pulls from but not in my form or the datasheet view of the query. I have narrowed the problem down to a relationship problem but the MS help and online help doesnt quite tell me enough. I believe that i need a one to one relationship, but when i add a table in relationship window, I get an error about "enforce referintial integrity" a rules violation. Can anyone help me figure out the next step to take? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
update queries and forms | Mary Pode | Database Design | 16 | February 2nd, 2005 05:25 PM |
Unmatched Query Mess | Natalia | Running & Setting Up Queries | 8 | October 28th, 2004 02:36 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
append Query duplicating records | Alex | Running & Setting Up Queries | 1 | July 8th, 2004 01:31 PM |