Access 2016 - Overwriting calc field is what I need, but it's not allowed.

Question

For Order Processing I use tblCustomers, tblOPMain (for order info), and tblOPDetail (for line items), along with a form for each table. Default shipping and invoicing address info is held in the Customers table and normally can be used in OPMain, for the current sales order. However, on occasions, either or both sets of address fields (especially shipping) need to be other than the default, particularly for multinational customers.

Using calculated fields, for ShipAddr1, InvAddr1, ShipAddr2, etc., makes it simple to autofill them but prevents overwriting. My current solution uses two buttons on OPMain which get the Shipping and Invoicing defaults from Customers (actually via hidden columns on OPMain's CustomerID combo) and stores them in bound fields ShipAddr1, etc. So the user can simply type in non-default address info, or else hit one or both buttons to fill in relevant defaults.

My concern is that most of the time I am storing the same information twice (i.e. in tblCustomers and in tblOPMain). Is there a more elegant solution which avoids the normalisation issues?

Any help would be much appreciated.

Answer 1

There is no normalization issue.  The values in the columns in the customers table are functionally determined solely by the key of that table, whereas, because they can differ from the 'default' values, the values in the same named columns in the orders table are functionally determined solely by the key  of that table.

This is directly analogous to the very common situation where both a Products table and an OrderDetails table contain a UnitPrice column.  The former can differ from the latter because prices change over time, so earlier orders will have different prices for the same product from a current order.  Again, in each case, the column is functionally determined solely by the key of the table in which it is located.  The tables are therefore Normalized to at least Third Normal Form (3NF) as there are no transitive dependencies.

---

Ken Sheridan, Stafford, England

Answer 2

Thank you, Ken,

As always, it seems, your response is speedy, precise and succinct ---- and most welcome. I wasn't sure about the normalization issue (or, indeed, the lack of it), and was hoping for exactly the expert "no problem" opinion that you provided.

Thanks again,

Norman