Template "Order Management" Error?

[jawzu]

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!

[Tom Wickerath]

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
__________________________________________

[Tom Wickerath]

Oops. I left out the OrderID field in the SQL statement. Thus, the Link
Master Field / Link Child Field properties were not working properly, causing
all subform records to be displayed for each order. Use this SQL statement
instead:

SELECT OrderID, ProductID, Quantity, UnitPrice, Discount,

CCur(([UnitPrice])*[Quantity]*(1-[Discount])) AS Price
FROM [Order Details];

I have not taken the time right now to see if my earlier suggestion of
deleting the Price field from the Order Details table will cause anything
else to break in this sample. I'll leave that for you to determine.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Joan Wild]

Tom Wickerath wrote:
>
> 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 Price field should be deleted from the Order Details table.

I have to disagree with you on this Tom. This is a case where storing a
calculated value does not break normalization rules. You need to store the
price in the order details tables, as that reflects the price of the item
*at the time it was ordered*. If you don't store the price, and the price
changes, all your historical records will use the new price, which isn't
what you'd want.

--
Joan Wild
Microsoft Access MVP

[Tom Wickerath]

Hi Joan,

> This is a case where storing a calculated value does not break
> normalization rules.

Sorry, but storing the results of any value that can be derived from other
data stored in the same table does break normalization rules.

> You need to store the price in the order details tables, as that reflects
> the price of the item *at the time it was ordered*.

I completely agree--no argument there. However, the price at the time an
order was placed is not a calculated value--it is simply copied from the
Products table in this case.

You didn't download the sample and take a look at it, did you? If you had,
you should have noticed that a field named UnitPrice is present in the Order
Details table. This represents the price at the time the order was placed.
However, they included another field named Price, which, when you examine the
data appears to be storing the results of a calculation, with one glaring
error for the price of the Black mugs:

Order Detail ID Order ID Product ID Quantity Unit Price Price
Discount
1 1 Black Mug 15 $5.00 $150.00
0.00%

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Douglas J. Steele]

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:9424B9CD-B823-4DA2...@microsoft.com...

>
> You didn't download the sample and take a look at it, did you? If you
> had,
> you should have noticed that a field named UnitPrice is present in the
> Order
> Details table. This represents the price at the time the order was placed.
> However, they included another field named Price, which, when you examine
> the
> data appears to be storing the results of a calculation, with one glaring
> error for the price of the Black mugs:
>
> Order Detail ID Order ID Product ID Quantity Unit Price Price
> Discount
> 1 1 Black Mug 15 $5.00 $150.00
> 0.00%

I haven't downloaded the sample either, but you've got to be putting me on!
15 items at $5.00 per item doesn't equate to $150.00.

Hopefully that's a typo, Tom.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

[Tom Wickerath]

Hi Doug,

Take a look at the screen shot, here:

http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033

I think this is called Microsoft Math.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[Joan Wild]

Tom Wickerath wrote:
> Hi Joan,

>
> You didn't download the sample and take a look at it, did you? If
> you had, you should have noticed that a field named UnitPrice is
> present in the Order Details table.

Sorry, no I didn't. I just did, and now see what you're saying. No need to
get snippy about it.

[Tom Wickerath]

Joan,

I wasn't getting snippy. However, you forced me to make a public reply by
posting a response that implied I was incorrect. Perhaps you would have been
better off to reply in the private newsgroup first.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[David W. Fenton]

Tom Wickerath <AOS168b AT comcast DOT net> wrote in

news:9424B9CD-B823-4DA2...@microsoft.com:

> You didn't download the sample and take a look at it, did you? If
> you had, you should have noticed that a field named UnitPrice is
> present in the Order Details table. This represents the price at
> the time the order was placed. However, they included another
> field named Price, which, when you examine the data appears to be
> storing the results of a calculation, with one glaring error for
> the price of the Black mugs:

I assume there's a tax rate field? If so, then it's a good thing to
store the extended price and tax total because then you don't have
to round all the numbers in reports and forms to get things to come
out right. Using Currency fields doesn't entirely eliminate the
rounding issues. If you've got much data, you'll find that doing the
rounding at presentation time, especially for aggregation, is going
to mean your queries take forever to execute.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tom Wickerath]

Hi David,

> I assume there's a tax rate field?

Yes, there appears to be, if you look in the lower left corner of this
screen shot:

http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033

> If so, then it's a good thing to store the extended price and tax total

> because then you don't have to....

That would be a case for breaking normalization rules. However, if one does
this, they should take to heart the advice that Michael Hernandez gives in
his database design paper, which I'll repeat here:

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>

At a minimum, one should make sure to implement JET check constraints at the
table level, to prevent the very problem displayed in the screen shot, ie.:

15 Black mugs @ $5.00 each somehow totals $150.00 and
9 Blue mugs @ $6.00 each displays a null extended price.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

[aaron...@gmail.com]

wow this is pretty much the most ridiculous statement i've ever heard
of

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."

it's just funny.. sometimes you MUST store calculated values.. I mean;
no ifs, ands-- or buts-- some times you MUST store calculated values;
it is called 'aggregates'

Funny thing is that these kids that run around with MDB have never been
introduced to data warehousing types of databases ROFL

-Aaron

[aaron...@gmail.com]

if only Microsoft let me speak in the private newsgroup; then maybe I
could talk to the real decision makers.. and not piss so many people
off

-Aaron

[Nathan Swartzlander]

Did you figure out how to make the orders management database work? If you
did, I would like to know. I have the same problem with it.

Thanks
Nathan

[Tom Wickerath]

Hi Nathan,

Did you not see the rest of the replies in this thread?

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 OrderID, 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

https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

[Nathan Swartzlander]

Thanks. I did read the rest of it but got bogged down with the extra
comments at the end. I will try what you said. Thanks for the info.

Nathan

[Nathan Swartzlander]

Can I put two sql statements together? I want to find the amount of discount
as a dollar amount, and find the total price?