R
Richard Horne
Hi guys, I have a bit of a complicated query here that I need some serious
help with. It's quite difficult to explain so bear with me.
The basic jist of the problem is that I need to calculate the quantity
remaining for an item by comparing the total delivered against that item
number with the total number ordered - the quantity remaining being the
difference. Which all sounds very easy - however my data is structured as
follows:
Tables:
Orders contains details of an order
Order_Details contains all the items in an order including Quantity
Deliveries contains details of a delivery including the Order_Number from
Orders.
Delivery_Details contains all the items delivered on that particular
delivery note, including Quantity_Delivered
Orders
========
OrderNumber
CustomerName
ContactName
OrderDate
Order_Details
===========
OrderNumber
ItemNumber
ItemType
Description
Quantity
Price
Deliveries
=========
DeliveryNoteID
OrderNumber
Date
DespatchMethod
Delivery_Details
==========
DeliveryNoteID
ItemNumber
QtyDelivered
ItemComplete
I have a form called Delivery_Notes where I enter the OrderNumber - this
queries all deliveries made against that order and allows you to enter more
deliveries.
So my understanding is that I need to create a new field called
Quantity_Remaining that is equal to Qty (from Order_Details) minus the total
sum of all QtyDelivered where the OrderNumber is equal to the OrderNumber
entered on the form.
help with. It's quite difficult to explain so bear with me.
The basic jist of the problem is that I need to calculate the quantity
remaining for an item by comparing the total delivered against that item
number with the total number ordered - the quantity remaining being the
difference. Which all sounds very easy - however my data is structured as
follows:
Tables:
Orders contains details of an order
Order_Details contains all the items in an order including Quantity
Deliveries contains details of a delivery including the Order_Number from
Orders.
Delivery_Details contains all the items delivered on that particular
delivery note, including Quantity_Delivered
Orders
========
OrderNumber
CustomerName
ContactName
OrderDate
Order_Details
===========
OrderNumber
ItemNumber
ItemType
Description
Quantity
Price
Deliveries
=========
DeliveryNoteID
OrderNumber
Date
DespatchMethod
Delivery_Details
==========
DeliveryNoteID
ItemNumber
QtyDelivered
ItemComplete
I have a form called Delivery_Notes where I enter the OrderNumber - this
queries all deliveries made against that order and allows you to enter more
deliveries.
So my understanding is that I need to create a new field called
Quantity_Remaining that is equal to Qty (from Order_Details) minus the total
sum of all QtyDelivered where the OrderNumber is equal to the OrderNumber
entered on the form.