Hi jawzu,
I assume you are talking about the sample located here:
http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033
Sadly, it appears as if the intern that Microsoft employed to create this
sample did not know squat about proper database design. They are storing the
Extended price in the Order Details table! The screen shot in the template
download page even shows an incorrect calculation. You can see quantity of 15
@ $5.00 each, with no discount, and the Extended Price is somehow shown as
$150.00. Pretty amazing, and sad.
Check out the following database design document:
http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization" in the Meeting
Downloads section)
<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>
As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."
The Price field should be deleted from the Order Details table. Change the
recordsource shown for the Order Details Subform from the Order Details
table, to the following SQL statement:
SELECT ProductID, Quantity, UnitPrice, Discount,
CCur(([UnitPrice])*[Quantity]*(1-[Discount])) AS Price
FROM [Order Details];
Alternately, you can save the SQL statement shown above as a new query, and
then specify this new query as the recordsource for the subform. My
preference is to avoid the buildup of too many saved queries, by using SQL
statements directly.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
jawzu said:
Hi,
I'm using the Access order management template and I've run in to some
problems.
On the Order Details Subform, the Extended Price is not updating when I
change the quantity or product. As the order subtotal pulls from the sum of
the extended price, the Order total is incorrect.
I see that the "Total Price of Order Detail" query shows the subtotal is
correct, but it's not feeding back to the Order Details Subform. I don't
know if this is a macro issue or a query issue, but I can't figure it out.
Has anyone using this template experienced this?
Thanks so much!