|
| 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
May 16th, 2009, 05:25 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
relationship inderteminate---Fred?
On May 4th i made a posting and only now i am working on my DB...I receive
the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. 
|
|
#2
May 16th, 2009, 06:50 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
Make sure the is a unique index on the field on the ONE side of the
relation. If the field is *not* already the primary key of your table, set its Indexed property (lower pane of table design view) to: Yes (No Duplicates) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "acss" wrote in message ... On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated.
|
|
#3
May 16th, 2009, 06:51 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
On Fri, 15 May 2009 21:25:01 -0700, acss wrote:
On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
#4
May 16th, 2009, 12:06 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
Thanks. The the PK INVOICEID is now the FK in the Invoice details table as a
one to many yet another problem is it will not allow me to enforce referential integrity. Each invoice has several invoice items(expenses such as fuel tax, vat tax, overtime fee) so would this cause a problem when entering records? "John W. Vinson" wrote: On Fri, 15 May 2009 21:25:01 -0700, acss wrote: On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
#5
May 16th, 2009, 03:23 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
If you cannot enforce RI, it may be because the Invoice Details
table has some values in the InvoiceID field that don't exist in the main Invoice table. As Allen Browne says - "Creating a relationship without Referential Integrity is little more than an excercise in drawing lines". BTW - you should bookmark his site - very informative. -- _________ Sean Bailey "acss" wrote: Thanks. The the PK INVOICEID is now the FK in the Invoice details table as a one to many yet another problem is it will not allow me to enforce referential integrity. Each invoice has several invoice items(expenses such as fuel tax, vat tax, overtime fee) so would this cause a problem when entering records? "John W. Vinson" wrote: On Fri, 15 May 2009 21:25:01 -0700, acss wrote: On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
#6
May 16th, 2009, 04:05 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
I have a total of four tables and though the others work well, i have just
added the new InvoiceDetails Table. The fields in the two tables are below and any direction is appreciated for designwould like to show the fields in the Ivoice table and Invoice Details table in the event it may help: InvoiceTable InvoiceID-Text-PK DetailID-Text-FK InvDate InvDescrip InvAmt InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK DetailDescription DetailAmt Once Again, I have one invoice and it can contain several items (Detailed expenses that create the total amount of the invoice). In this setup, i assume it is one to many.Is this correct? "Beetle" wrote: If you cannot enforce RI, it may be because the Invoice Details table has some values in the InvoiceID field that don't exist in the main Invoice table. As Allen Browne says - "Creating a relationship without Referential Integrity is little more than an excercise in drawing lines". BTW - you should bookmark his site - very informative. -- _________ Sean Bailey "acss" wrote: Thanks. The the PK INVOICEID is now the FK in the Invoice details table as a one to many yet another problem is it will not allow me to enforce referential integrity. Each invoice has several invoice items(expenses such as fuel tax, vat tax, overtime fee) so would this cause a problem when entering records? "John W. Vinson" wrote: On Fri, 15 May 2009 21:25:01 -0700, acss wrote: On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
#7
May 16th, 2009, 04:59 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
A couple of things to note here.
First, the Invoice table should not have DetailID as a FK field. The "one" side table doesn't conatin a FK, the "many" side table does. Also, the total amount of the invoice would be calculated based on the sum of the values from the InvoiceDetails table. Calculated values like this should not be stored, so your InvAmt field should not exist at all. A revised table structure; InvoiceTable InvoiceID-Text-PK InvDate InvDescrip InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK to InvoiceTable DetailDescription DetailAmt If you InvoiceDetails table is new, and has no records, then I see no reason why you would not be able to enforce RI on a relationship between the two InvoiceID fields base on what you've posted. -- _________ Sean Bailey "acss" wrote: I have a total of four tables and though the others work well, i have just added the new InvoiceDetails Table. The fields in the two tables are below and any direction is appreciated for designwould like to show the fields in the Ivoice table and Invoice Details table in the event it may help: InvoiceTable InvoiceID-Text-PK DetailID-Text-FK InvDate InvDescrip InvAmt InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK DetailDescription DetailAmt Once Again, I have one invoice and it can contain several items (Detailed expenses that create the total amount of the invoice). In this setup, i assume it is one to many.Is this correct? "Beetle" wrote: If you cannot enforce RI, it may be because the Invoice Details table has some values in the InvoiceID field that don't exist in the main Invoice table. As Allen Browne says - "Creating a relationship without Referential Integrity is little more than an excercise in drawing lines". BTW - you should bookmark his site - very informative. -- _________ Sean Bailey "acss" wrote: Thanks. The the PK INVOICEID is now the FK in the Invoice details table as a one to many yet another problem is it will not allow me to enforce referential integrity. Each invoice has several invoice items(expenses such as fuel tax, vat tax, overtime fee) so would this cause a problem when entering records? "John W. Vinson" wrote: On Fri, 15 May 2009 21:25:01 -0700, acss wrote: On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
#8
May 16th, 2009, 07:03 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
Thank you since now with your design i am able to have RI.I am very confused
as far as the fields for invamt in my invoice table since my idea is to have a form which a user can key in data populating the invoice and then there would be fields specifically for the detailed expenses which would give the final invoice total. Later on i need to run queries which will identify each spefic charge. Am i wrong on my process? "Beetle" wrote: A couple of things to note here. First, the Invoice table should not have DetailID as a FK field. The "one" side table doesn't conatin a FK, the "many" side table does. Also, the total amount of the invoice would be calculated based on the sum of the values from the InvoiceDetails table. Calculated values like this should not be stored, so your InvAmt field should not exist at all. A revised table structure; InvoiceTable InvoiceID-Text-PK InvDate InvDescrip InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK to InvoiceTable DetailDescription DetailAmt If you InvoiceDetails table is new, and has no records, then I see no reason why you would not be able to enforce RI on a relationship between the two InvoiceID fields base on what you've posted. -- _________ Sean Bailey "acss" wrote: I have a total of four tables and though the others work well, i have just added the new InvoiceDetails Table. The fields in the two tables are below and any direction is appreciated for designwould like to show the fields in the Ivoice table and Invoice Details table in the event it may help: InvoiceTable InvoiceID-Text-PK DetailID-Text-FK InvDate InvDescrip InvAmt InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK DetailDescription DetailAmt Once Again, I have one invoice and it can contain several items (Detailed expenses that create the total amount of the invoice). In this setup, i assume it is one to many.Is this correct? "Beetle" wrote: If you cannot enforce RI, it may be because the Invoice Details table has some values in the InvoiceID field that don't exist in the main Invoice table. As Allen Browne says - "Creating a relationship without Referential Integrity is little more than an excercise in drawing lines". BTW - you should bookmark his site - very informative. -- _________ Sean Bailey "acss" wrote: Thanks. The the PK INVOICEID is now the FK in the Invoice details table as a one to many yet another problem is it will not allow me to enforce referential integrity. Each invoice has several invoice items(expenses such as fuel tax, vat tax, overtime fee) so would this cause a problem when entering records? "John W. Vinson" wrote: On Fri, 15 May 2009 21:25:01 -0700, acss wrote: On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
#9
May 16th, 2009, 11:55 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
The reason you don't store calculated values like this is that
if one of the underlying values that the calculation is based on gets changed, the stored value is NOT automatically recalculated. So you can easily end up with incorrect data in your InvAmt field. You should do this calculation using an unbound control on your form or report, but the value should not be stored in a table, just calculated as needed (in other words, everytime you open your form, change records, or enter new base values, etc., the total is recalculated). You can also do calculations in queries, but in the scenario you described it would most likely be done in an unbound control on a form or report. -- _________ Sean Bailey "acss" wrote: Thank you since now with your design i am able to have RI.I am very confused as far as the fields for invamt in my invoice table since my idea is to have a form which a user can key in data populating the invoice and then there would be fields specifically for the detailed expenses which would give the final invoice total. Later on i need to run queries which will identify each spefic charge. Am i wrong on my process? "Beetle" wrote: A couple of things to note here. First, the Invoice table should not have DetailID as a FK field. The "one" side table doesn't conatin a FK, the "many" side table does. Also, the total amount of the invoice would be calculated based on the sum of the values from the InvoiceDetails table. Calculated values like this should not be stored, so your InvAmt field should not exist at all. A revised table structure; InvoiceTable InvoiceID-Text-PK InvDate InvDescrip InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK to InvoiceTable DetailDescription DetailAmt If you InvoiceDetails table is new, and has no records, then I see no reason why you would not be able to enforce RI on a relationship between the two InvoiceID fields base on what you've posted. -- _________ Sean Bailey "acss" wrote: I have a total of four tables and though the others work well, i have just added the new InvoiceDetails Table. The fields in the two tables are below and any direction is appreciated for designwould like to show the fields in the Ivoice table and Invoice Details table in the event it may help: InvoiceTable InvoiceID-Text-PK DetailID-Text-FK InvDate InvDescrip InvAmt InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK DetailDescription DetailAmt Once Again, I have one invoice and it can contain several items (Detailed expenses that create the total amount of the invoice). In this setup, i assume it is one to many.Is this correct? "Beetle" wrote: If you cannot enforce RI, it may be because the Invoice Details table has some values in the InvoiceID field that don't exist in the main Invoice table. As Allen Browne says - "Creating a relationship without Referential Integrity is little more than an excercise in drawing lines". BTW - you should bookmark his site - very informative. -- _________ Sean Bailey "acss" wrote: Thanks. The the PK INVOICEID is now the FK in the Invoice details table as a one to many yet another problem is it will not allow me to enforce referential integrity. Each invoice has several invoice items(expenses such as fuel tax, vat tax, overtime fee) so would this cause a problem when entering records? "John W. Vinson" wrote: On Fri, 15 May 2009 21:25:01 -0700, acss wrote: On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
#10
May 17th, 2009, 01:31 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
relationship inderteminate---Fred?
Thanks for the explaination. The invoices received will be static and
expenses will not be changed so in my thoughts all are constant. The invoices received and entered into the DB are final with no changes...i need to track all expenses and that is the reason fo this DB. As invoices are received they will be entered into he DB and later on a report based on queiries will details all charges per invoice and contractor. In this sceanario will the design be sufficient or additional changes need to be made. I only beleive that is will need a combo box so each invoice on a form can have each invoice details expense entered as a separate item per invoice to get a grand total per invoice. Is there something wrong with this idea or design? "Beetle" wrote: The reason you don't store calculated values like this is that if one of the underlying values that the calculation is based on gets changed, the stored value is NOT automatically recalculated. So you can easily end up with incorrect data in your InvAmt field. You should do this calculation using an unbound control on your form or report, but the value should not be stored in a table, just calculated as needed (in other words, everytime you open your form, change records, or enter new base values, etc., the total is recalculated). You can also do calculations in queries, but in the scenario you described it would most likely be done in an unbound control on a form or report. -- _________ Sean Bailey "acss" wrote: Thank you since now with your design i am able to have RI.I am very confused as far as the fields for invamt in my invoice table since my idea is to have a form which a user can key in data populating the invoice and then there would be fields specifically for the detailed expenses which would give the final invoice total. Later on i need to run queries which will identify each spefic charge. Am i wrong on my process? "Beetle" wrote: A couple of things to note here. First, the Invoice table should not have DetailID as a FK field. The "one" side table doesn't conatin a FK, the "many" side table does. Also, the total amount of the invoice would be calculated based on the sum of the values from the InvoiceDetails table. Calculated values like this should not be stored, so your InvAmt field should not exist at all. A revised table structure; InvoiceTable InvoiceID-Text-PK InvDate InvDescrip InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK to InvoiceTable DetailDescription DetailAmt If you InvoiceDetails table is new, and has no records, then I see no reason why you would not be able to enforce RI on a relationship between the two InvoiceID fields base on what you've posted. -- _________ Sean Bailey "acss" wrote: I have a total of four tables and though the others work well, i have just added the new InvoiceDetails Table. The fields in the two tables are below and any direction is appreciated for designwould like to show the fields in the Ivoice table and Invoice Details table in the event it may help: InvoiceTable InvoiceID-Text-PK DetailID-Text-FK InvDate InvDescrip InvAmt InvCde InvNotes InvoiceDetails Table DetailID-Text-PK InvoiceID-Text-FK DetailDescription DetailAmt Once Again, I have one invoice and it can contain several items (Detailed expenses that create the total amount of the invoice). In this setup, i assume it is one to many.Is this correct? "Beetle" wrote: If you cannot enforce RI, it may be because the Invoice Details table has some values in the InvoiceID field that don't exist in the main Invoice table. As Allen Browne says - "Creating a relationship without Referential Integrity is little more than an excercise in drawing lines". BTW - you should bookmark his site - very informative. -- _________ Sean Bailey "acss" wrote: Thanks. The the PK INVOICEID is now the FK in the Invoice details table as a one to many yet another problem is it will not allow me to enforce referential integrity. Each invoice has several invoice items(expenses such as fuel tax, vat tax, overtime fee) so would this cause a problem when entering records? "John W. Vinson" wrote: On Fri, 15 May 2009 21:25:01 -0700, acss wrote: On May 4th i made a posting and only now i am working on my DB...I receive the error of relationship inderteminate when using the INVOICE ID field from my Invoice table as a foreign key in the Invoice details table. In essense, one invoice may have many expense details yet i am unable to make the relationship due to this error. Any direction would be appreciated. Do you have a unique index - such as a Primary Key - on the [INVOICE ID] field in the Invoice table? If not you'll get this error. -- John W. Vinson [MVP]
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
