M
MacLean
Tom
This is repost from last Thursday. I'm sorry I couldn't reply on Friday, we were off due to the holiday
In regards to your questions below; yes, there are cases where a record from Table1 doesn't have
corresponding record in Table2 and viceversa, and I do need to see all of them regardless. The missin
fields on such records should default to 0. I'm running on Jet
Also, the intransit qty I'm trying to calculate is a running total of the previous intransit qty (from the previous
most recent record that matches the CustPartNo and CustLocation) plus the current QtyShipped minus th
current QtyReceived
Thank you for your help
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 ro
in Table2? Is it possible to have a row in Table2 without an
corresponding row in Table1? The answer to this determines what kin
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 QtyReceive
FROM Table1 T
INNER JOIN Table2 T2 ON T2.CustLocation = T1.CustLocatio
AND T2.CustPartNo = T1.CustPartNo AND T2.TranDate = T1.TranDat
GROUP BY T1.CustLocation, T1.CustPartNo, T1.TranDat
ORDER BY T1.CustLocation, T1.CustPartNo, T1.TranDat
If there are any cases where the CustLocation / CustPartNo / TranDat
is in one table but not the other, that entire CustLocation
CustPartNo / TranDate combination will be completely missing from th
results. In some cases, this is desirable, but I'm betting it isn'
what you want
If you want the full results, no matter what might be missing, yo
need the FULL JOIN. Access Jet doesn't do this, but MSDE does. Ther
is a work-around for Jet, however. Please respond with informatio
about which case you have and I can produce the specific query yo
need
Tom Elliso
Microsoft Access MV
Ellison Enterprises - Your One Stop IT Expert
On Thu, 8 Apr 2004 06:46:02 -0700, "MacLean
This is repost from last Thursday. I'm sorry I couldn't reply on Friday, we were off due to the holiday
In regards to your questions below; yes, there are cases where a record from Table1 doesn't have
corresponding record in Table2 and viceversa, and I do need to see all of them regardless. The missin
fields on such records should default to 0. I'm running on Jet
Also, the intransit qty I'm trying to calculate is a running total of the previous intransit qty (from the previous
most recent record that matches the CustPartNo and CustLocation) plus the current QtyShipped minus th
current QtyReceived
Thank you for your help
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 ro
in Table2? Is it possible to have a row in Table2 without an
corresponding row in Table1? The answer to this determines what kin
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 QtyReceive
FROM Table1 T
INNER JOIN Table2 T2 ON T2.CustLocation = T1.CustLocatio
AND T2.CustPartNo = T1.CustPartNo AND T2.TranDate = T1.TranDat
GROUP BY T1.CustLocation, T1.CustPartNo, T1.TranDat
ORDER BY T1.CustLocation, T1.CustPartNo, T1.TranDat
If there are any cases where the CustLocation / CustPartNo / TranDat
is in one table but not the other, that entire CustLocation
CustPartNo / TranDate combination will be completely missing from th
results. In some cases, this is desirable, but I'm betting it isn'
what you want
If you want the full results, no matter what might be missing, yo
need the FULL JOIN. Access Jet doesn't do this, but MSDE does. Ther
is a work-around for Jet, however. Please respond with informatio
about which case you have and I can produce the specific query yo
need
Tom Elliso
Microsoft Access MV
Ellison Enterprises - Your One Stop IT Expert
On Thu, 8 Apr 2004 06:46:02 -0700, "MacLean