Prev: Sharing a database with multi-users
Next: Designing a patient management
From: Rachel on 9 Mar 2010 23:23
So - This is what I am trying to achieve:

In our business, our orders are made up of products (pizzas actually!) to
which the customer can add or remove toppings. Adding a topping costs $1.00.

My AddAnOrderandDetailsform captures the customer.

My OrderDetailsSubform captures the products (S Margherita, for example),
the unit price, quantity, and extended price (yes calculated by multiplying
Quantity ad UnitPrice).

I then have my ItemDetailsSubform which I want to capture the additional
toppings or toppings removed, eg + Pepperoni or - Pepperoni.

Both my OrderDetailsSubform and ItemDetailsSubform are datasheet view.
The prices load automatically using an afterupdate code on the cboProductID
on each and the txtOrderDetailsTotal and txtItemDetailsTotal calculate, as
mentioned.
__________________________________________________________________
1. Ideally I would like the cost of the additional toppings to be added to
the extendedprice of the item on the OrderDetailssubform and hence the
GrandTotal on the AddAnOrderandDetailsform or, if this is too tricky, just to
the GrandTotal on the AddAnOrderandDetailsform. Either way this cost needs to
be included in the final price.

2. I would like the ItemDetailsSubform to load when I change a combo box on
the OrderDetailsSubform (cboplus/minus) to either + or -. At the moment it
loads but the record won't save as the OrderDetails record hasn't saved yet,
therefore it has no corresponding record. I have managed to get it to link to
the OrderDetails record via the OrderID but how do I get this to save at the
same time as loading the ItemDetails subform?

4. Besides all this I have read that you shouldn't 'save' the price of
orders, that they should only ever be calculated controls. I'm not sure I
understand this correctly. If the price is always calculated what happens if
the price of a product changes, and I go in to the change that price,
wouldn't all the past orders with that product change? I don't want this as I
want to be able to look back and compare etc.

Any comments or suggestions or codes or help or guidance or blank stares are
much appreciated!!!
:)

Thanks,
Rachel

From: Tom van Stiphout on 10 Mar 2010 09:08
On Tue, 9 Mar 2010 20:23:01 -0800, Rachel
<Rachel(a)discussions.microsoft.com> wrote:

I'm a bit short on time right now - may be back later. I'll just
tackle Q4:
The general rule is: orders work with current prices, invoices have
stored prices.
You are trying to do both at the same time: take the order and store
invoice data. You need 5 tables: orders, orderdetails, products (with
current prices), invoices, and invoicedetails. The price is copied
into the invoicedetail table, so you can record the price as it was on
that day.

-Tom.
Microsoft Access MVP


>So - This is what I am trying to achieve:
>
>In our business, our orders are made up of products (pizzas actually!) to
>which the customer can add or remove toppings. Adding a topping costs $1.00.
>
>My AddAnOrderandDetailsform captures the customer.
>
>My OrderDetailsSubform captures the products (S Margherita, for example),
>the unit price, quantity, and extended price (yes calculated by multiplying
>Quantity ad UnitPrice).
>
>I then have my ItemDetailsSubform which I want to capture the additional
>toppings or toppings removed, eg + Pepperoni or - Pepperoni.
>
>Both my OrderDetailsSubform and ItemDetailsSubform are datasheet view.
>The prices load automatically using an afterupdate code on the cboProductID
>on each and the txtOrderDetailsTotal and txtItemDetailsTotal calculate, as
>mentioned.
>__________________________________________________________________
>1. Ideally I would like the cost of the additional toppings to be added to
>the extendedprice of the item on the OrderDetailssubform and hence the
>GrandTotal on the AddAnOrderandDetailsform or, if this is too tricky, just to
>the GrandTotal on the AddAnOrderandDetailsform. Either way this cost needs to
>be included in the final price.
>
>2. I would like the ItemDetailsSubform to load when I change a combo box on
>the OrderDetailsSubform (cboplus/minus) to either + or -. At the moment it
>loads but the record won't save as the OrderDetails record hasn't saved yet,
>therefore it has no corresponding record. I have managed to get it to link to
>the OrderDetails record via the OrderID but how do I get this to save at the
>same time as loading the ItemDetails subform?
>
>4. Besides all this I have read that you shouldn�t �save� the price of
>orders, that they should only ever be calculated controls. I�m not sure I
>understand this correctly. If the price is always calculated what happens if
>the price of a product changes, and I go in to the change that price,
>wouldn�t all the past orders with that product change? I don�t want this as I
>want to be able to look back and compare etc.
>
>Any comments or suggestions or codes or help or guidance or blank stares are
>much appreciated!!!
>:)
>
>Thanks,
>Rachel
From: Rachel on 11 Mar 2010 06:27
Thanks very much for taking the time to reply Tom.
Your brief words have helped immensely but have also opened up a whole other
can of beans! I have an orders, orderdetails and products table (as well as a
table for: Additional Toppings, Deals, OrderType, Payment Methods, Payments,
Product Sizes, and Item Details). I will attempt to incorporate the invoice
and invoicedetails table whilst I eagerly await your next installment!

Thanks once again,
Rachel

"Tom van Stiphout" wrote:

> On Tue, 9 Mar 2010 20:23:01 -0800, Rachel
> <Rachel(a)discussions.microsoft.com> wrote:
>
> I'm a bit short on time right now - may be back later. I'll just
> tackle Q4:
> The general rule is: orders work with current prices, invoices have
> stored prices.
> You are trying to do both at the same time: take the order and store
> invoice data. You need 5 tables: orders, orderdetails, products (with
> current prices), invoices, and invoicedetails. The price is copied
> into the invoicedetail table, so you can record the price as it was on
> that day.
>
> -Tom.
> Microsoft Access MVP
>
>
> >So - This is what I am trying to achieve:
> >
> >In our business, our orders are made up of products (pizzas actually!) to
> >which the customer can add or remove toppings. Adding a topping costs $1.00.
> >
> >My AddAnOrderandDetailsform captures the customer.
> >
> >My OrderDetailsSubform captures the products (S Margherita, for example),
> >the unit price, quantity, and extended price (yes calculated by multiplying
> >Quantity ad UnitPrice).
> >
> >I then have my ItemDetailsSubform which I want to capture the additional
> >toppings or toppings removed, eg + Pepperoni or - Pepperoni.
> >
> >Both my OrderDetailsSubform and ItemDetailsSubform are datasheet view.
> >The prices load automatically using an afterupdate code on the cboProductID
> >on each and the txtOrderDetailsTotal and txtItemDetailsTotal calculate, as
> >mentioned.
> >__________________________________________________________________
> >1. Ideally I would like the cost of the additional toppings to be added to
> >the extendedprice of the item on the OrderDetailssubform and hence the
> >GrandTotal on the AddAnOrderandDetailsform or, if this is too tricky, just to
> >the GrandTotal on the AddAnOrderandDetailsform. Either way this cost needs to
> >be included in the final price.
> >
> >2. I would like the ItemDetailsSubform to load when I change a combo box on
> >the OrderDetailsSubform (cboplus/minus) to either + or -. At the moment it
> >loads but the record won't save as the OrderDetails record hasn't saved yet,
> >therefore it has no corresponding record. I have managed to get it to link to
> >the OrderDetails record via the OrderID but how do I get this to save at the
> >same time as loading the ItemDetails subform?
> >
> >4. Besides all this I have read that you shouldn't 'save' the price of
> >orders, that they should only ever be calculated controls. I'm not sure I
> >understand this correctly. If the price is always calculated what happens if
> >the price of a product changes, and I go in to the change that price,
> >wouldn't all the past orders with that product change? I don't want this as I
> >want to be able to look back and compare etc.
> >
> >Any comments or suggestions or codes or help or guidance or blank stares are
> >much appreciated!!!
> >:)
> >
> >Thanks,
> >Rachel
> .
>
 | 
Pages: 1
Prev: Sharing a database with multi-users
Next: Designing a patient management