Comparing fields from two different tables to give quantity remain

[Guest]

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.

 

[Michel Walsh]

Hi,


Indeed, that is what you have to do. From the query designer, bring the
required tables, then click on the summation button (the one with a capital
Sigma, a kind of M rotated by 90 degree) and a new line, Total, appear in
the grid. Use the default GROUP on Items, also on Order.DateTime, and use
SUM on quantity fields. You can use EXPRESSION to subtract a SUM from
another one: SUM(a.qty) - SUM(b.qty). You use WHERE, always in the new
line Total, to specify a criteria that has to occur before the summation,
such a to specify, under the field a.DateTime, the requirement that
b.DateTime should be less or equal to it (to consider only the arrival that
occurred before the sale).



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

This didn't seem to work so can someone explain in a bit more details of
suggest an alternative way of doing this?