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 said:
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 said:
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
:
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?
:
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
:
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?
:
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.
