how to get previous calculated field

[MacLean]

I’m trying to create a query to track the product that is in-transit to the customers
I have 2 tables to get the data from and calculate the in-transit qty to each customer location for each product

Table1 is built from a daily shipments data file sent by our shipping warehouses to provide the qty shipped for each product to each customer location
CustPartNo, PartNo, CustLocation, QtyShipped, TranDat

Table2 is built from a daily receipts data file sent by the customers to provide the qty received for each product
CustPartNo, CustLocation, QtyReceived, TranDat

The query should contain the following fields
CustLocation, TranDate, CustPartNo, PartNo, QtyShipped, QtyReceived, QtyInTransi

For each customer location, a new record should be generated for each product that was shipped AND/OR received. All records from both tables should be in the query, but those where the CustLocation, TranDate and CustPartNo match; should appear only once. The QtyInTransit should be calculated as follows

QtyInTransit = QtyIntransit (from the most recent record that matches the CustPartNo and CustLocation) + QtyShipped – QtyReceive

What’s the proper way to join these 2 tables so that all records from each table are picked up without duplicating the ones with the same CustLocation, TranDate and CustPartNo

How can I get the QtyInTransit from the most recent record that matches the CustPartNo and CustLocation to use in the calculation for the QtyInTransit of the current record? (0 should be assumed if no previous record is found

Thanks for your help!!

 

[Tom Ellison]

Dear MacLean:

It sounds like you need a JOIN between the two tables on CustLocation,
CustPartNo, and TranDate, showing a SUM of QtyShipped and QtyReceived.

Is it possible to have a row in Table1 without any corresponding row
in Table2? Is it possible to have a row in Table2 without any
corresponding row in Table1? The answer to this determines what kind
of join you will need: INNER, LEFT/RIGHT, or FULL.

Here's the way when the answer to both questions is 'no':

SELECT T1.CustLocation, T1.CustPartNo, T1.TranDate,
SUM(T1.QtyShipped) AS QtyShipped,
SUM(T2.QtyReceived) AS QtyReceived
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.CustLocation = T1.CustLocation
AND T2.CustPartNo = T1.CustPartNo AND T2.TranDate = T1.TranDate
GROUP BY T1.CustLocation, T1.CustPartNo, T1.TranDate
ORDER BY T1.CustLocation, T1.CustPartNo, T1.TranDate

If there are any cases where the CustLocation / CustPartNo / TranDate
is in one table but not the other, that entire CustLocation /
CustPartNo / TranDate combination will be completely missing from the
results. In some cases, this is desirable, but I'm betting it isn't
what you want.

If you want the full results, no matter what might be missing, you
need the FULL JOIN. Access Jet doesn't do this, but MSDE does. There
is a work-around for Jet, however. Please respond with information
about which case you have and I can produce the specific query you
need.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts