Orders lock/unlock

[mseo]

hi,
I have Follow-Up report for orders and deliveries to calculate the Build Quantities (the quantity I have to produce to fill the order) after subtracting the quantities delivered to the customer
attached to this post a snapshot of the old report
the trick that I couldn't get it solved in the report I added lock/unlock field to orders table to flag locked orders
so, I need to assign the delivery receipt to specific order
this is the point that I can't handle
the main problem in this report that I can't define from which Order the item will be subtracted
I will explain the problem using an example
assume that every month we get an order from a customer, but this order ends up by the end of the month
the order was:
10 monitors 1/1/2010
10 monitors 1/16/2010
we delivered 5 Monitors only, so the delivered quantities shouldn't included in the total of delivered quantities when I start the new order
and by the beggining the of the new month
we received new order:
20 monitors 2/1/2010
20 Monitors 2/18/2010
and delivered 15

so I need the report to be like this:
10 required _ 1/1/2010 _5 delivered _ 5 build quantity (locked)
10 required _ 1/16/2010 _0 delivered _ 10 build quantity (locked)
20 required _ 2/1/2010 _15 delivered _ 5 build quantity (opened)(curren t Month)
20 required _ 2/18/2010 _0 delivered _ 20 build quantity (opened)(curren t Month)
the period month is a metaphor here
locking the order will govern the period

any help would be appreciated
thank you very much

[MMcCarthy]

I haven't had time to check out your database but I'm guessing the problem is in your table structures. You have to have a reference to a particular order in a delivery.

So for example ...

tblOrder
OrderID (PK)
CustomerID (FK to Customer Table)
OrderDate
Locked (Y/N data type)

Assuming you could have multiple products on one order.
tblOrderDetails
OrderDetailID (PK)
OrderID (FK to the Orders table)
ProductID (FK to the Products table)
Quantity
CostPerItem

tblDelivery
DeliveryID (PK)
DeliveryDate
OrderID (FK to Orders table)
ProductID (FK to Products table)
Quantity

Now a query something like the following.

SELECT tblOrder.CustomerID, tblOrder.OrderID, tblOrderDetails.ProductID, tblOrderDetails.Quantity, tblOrder.OrderDate, tblDelivery.ProductID, tblDelivery.Quantity, tblOrder.Locked
FROM (tblOrder INNER JOIN tblOrderDetails
ON tblOrder.OrderID = tblOrderDetails.OrderID)
LEFT JOIN tblDelivery
ON tblOrder.OrderID = tblDelivery.OrderID

should give you the results you are looking for.

Mary

[mseo]

hi, Mary
thank you very much
I have a form for adding delivery receipts, if the allocating of the delivery receipt against orders may has multiple rows for one product, I need one more help I need to check the quantity of the record in table orderdetails because may be the quantity in the record of table deliverydetails exceeds the quantity in table ordersdetails

thank you very much

[MMcCarthy]

OK, so change tblDelivery to ...

tblDelivery
DeliveryID (PK)
DeliveryDate

and create a new table as follows ...

tblDeliverPerOr der
DeliveryID (PK - FK to Delivery table)
OrderID (PK - FK to Orders table)
ProductID (FK to Products table)
Quantity

The two things you have to remember here are that you have a joint primary key using both DeliveryID and OrderID. If you have two order with a quantity of 10 on one order and 15 on the next and you have a delivery of 20 then its up to you to assign 10 to order 1 and 10 to order 2. This joint table is necessary because an order can be done in more than one delivery and a delivery can contain more than one order. This is known as a many to many relationship and can only be handled by using a join table.

Now your query will be something like the following.

SELECT tblOrder.CustomerID, tblOrder.OrderID, tblOrderDetails.ProductID, tblOrderDetails.Quantity, tblOrder.OrderDate, tblDeliverPerOrder.ProductID, tblDeliverPerOrder.Quantity, tblOrder.Locked
FROM (tblOrder INNER JOIN tblOrderDetails
ON tblOrder.OrderID = tblOrderDetails.OrderID)
LEFT JOIN tblDeliverPerOrder
ON tblOrder.OrderID = tblDeliverPerOrder.OrderID
LEFT JOIN tblDelivery
ON tblDeliverPerOrder.DeliveryID=tblDelivery.DeliveryID

[mseo]

hi, Mary
when I tried to use the sql statement i have met a problem with left join
here's the structure of the tables in the Database
1- tbl_order

OrderID; AutoNumber; PK
OrderNo; String
CustomerID; Number; FK
OrderDate; Date
Locked; boolean

2- tbl_OrderDetail

OrderDetailID; AutoNumber;PK
OrderID; Number; PK( to allow duplication in products)
ProductID; Number; FK
DateRequired; Date
Quantity; Number
UnitPrice; number

3- tbl_deliveringr eceipts

ReceiptNo; String; PK
CustomerID; Number; FK
DateDelivered; Date
Cancelled; Boolean

4- tbl_deliveringr eceiptDetails

ReceiptNo; String; FK
ProductID; Number; FK
Quantity; number
unitprice; number
orderID; number; FK

thank you very much
I really apppreciate your help

[MMcCarthy]

I don't under stand why you have a joint primary key on this table.

tbl_OrderDetail
OrderDetailID; AutoNumber;PK
OrderID; Number; PK( to allow duplication in products)
ProductID; Number; FK
DateRequired; Date
Quantity; Number
UnitPrice; number

The order detail is a subset of the order. So I have an order for customer A and I create an order detail allowing for multiple products and quantities. Each of those details is for a separate item on the order. As the OrderDetailID is an autonumber it will never duplicate so I can't see why you have added OrderID as a primary key. It should only be a foreign key.

This is the reason why the left join on the query is causing problems because a joint primary key requires you to join on both fields.

[mseo]

thank you Mary

it works great

I really appreciate your help

[MMcCarthy]

You are welcome :)