Calculate total

[Ron]

Hi all,

I have table Orders : Product, Price, Quantity, DeliveryDay

Each record is a weekly order. Changes of the order are made on the record
itself to avoid creating new order.
I need be able to calculate total sale per order between 2 dates.

I was thinking to add to the table 2 date fields: StartOrder, EndOrder and
calculate how many DeliveryDay between them (N) and then
Price*Quantity*N = TotalSale

1. what if the order still active and there is no end date?
2. Is there a better way to accomplish this?

Thanks,

Ron

 

[OfficeDev18 via AccessMonster.com]

Ron,

It sounds like your data is not normalized. Ideally, two tables should be
used, one perhaps called tblOrders, which would contain information like
Order Number, Revision, Customer Number, Customer PO Number, Salesperson,
Order Date, Shipped?, Date Shipped, Total Price, etc. ad amnauseum. A second
table, maybe called tblOrderDetails, would have the order breakdown, like
Order Number (to provide a relationship to the tblOrders table), Revision
(ditto), Product Code, Product Qty, Product Price, Extension, etc., etc. If
you can set up your data in the way I described, you will have no problem
whatsoever doing what you want, unless I'm missing something.

If an order has been initialized but not shipped, it would normally not be
reported on in the same report as shipped orders.

If an order has been partially shipped, the program would automatically
increment the Revision field and initialize a new order, where the Order
Number wouldbe the same, but not the Revision.

There are many ways to skin the cat. I'm only trying to give you one idea of
how to set up a workable control program.

Hope this helps,

Sam

 

[Ron]

Thank you Sam.
The idea behind this structure is:
Because these are constant orders, every week at the same day, the same
customer gets the same quantity of the same product at the same price. I
wanted to avoid the salesperson to enter the same order evey week.
Changes in orders are rarly, but happens.
Thanks,
Ron

 

[OfficeDev18 via AccessMonster.com]

H'mm. Ron, I don't see a place to put in the order number or customer's
name/ID there. Anyway, you could make a query like this:

SELECT Sum(Price*Quantity) As Extension, DeliveryDay FROM Orders WHERE
DeliveryDay Between [Enter Start Date] And [Enter End Date] GROUP BY
DeliveryDay;

If you add order number and/or customer ID fields, you could maybe get a bit
more detail, if that's what you want.

You could probably copy and paste the above SQL into Access' query designer's
SQL window. Try running it, and see what it does.

Hope this helps,

Sam

Thank you Sam.
The idea behind this structure is:
Because these are constant orders, every week at the same day, the same
customer gets the same quantity of the same product at the same price. I
wanted to avoid the salesperson to enter the same order evey week.
Changes in orders are rarly, but happens.
Thanks,
Ron

Ron,

[quoted text clipped - 44 lines]