Full Outer Join

  • Thread starter Thread starter Gregg
  • Start date Start date
G

Gregg

I need to full outer join two tables while simultaneously
adding together two common fields. I've read the past
posts but am still struggling with getting thsi join to
work without a syntax error. This is what I have:

INSERT INTO StockOnHand ( prodIdentity, whsIdentity,
locIdentity, quantity, EffectiveDate, EffectiveTime,
ExpirationDate, LotNo )
SELECT StockOnHandLots.prodIdentity,
StockOnHandLots.whsIdentity, StockOnHandLots.locIdentity,
Sum([stockonhandARI].[quantity]+[stockonhandLots].
[quantity]) AS Qty, StockOnHandLots.EffectiveDate,
StockOnHandLots.EffectiveTime,
StockOnHandLots.ExpirationDate, StockOnHandLots.LotNo
FROM StockOnHandARI RIGHT JOIN StockOnHandLots ON
StockOnHandARI.prodIdentity = StockOnHandLots.prodIdentity
GROUP BY StockOnHandLots.prodIdentity,
StockOnHandLots.whsIdentity, StockOnHandLots.locIdentity,
StockOnHandLots.EffectiveDate,
StockOnHandLots.EffectiveTime,
StockOnHandLots.ExpirationDate, StockOnHandLots.LotNo
UNION
SELECT StockOnHandLots.prodIdentity,
StockOnHandLots.whsIdentity, StockOnHandLots.locIdentity,
Sum([stockonhandARI].[quantity]+[stockonhandLots].
[quantity]) AS Qty, StockOnHandLots.EffectiveDate,
StockOnHandLots.EffectiveTime,
StockOnHandLots.ExpirationDate, StockOnHandLots.LotNo
FROM StockOnHandLots RIGHT JOIN StockOnHandARI ON
StockOnHandARI.prodIdentity = StockOnHandLots.prodIdentity
GROUP BY StockOnHandLots.prodIdentity,
StockOnHandLots.whsIdentity, StockOnHandLots.locIdentity,
StockOnHandLots.EffectiveDate,
StockOnHandLots.EffectiveTime,
StockOnHandLots.ExpirationDate, StockOnHandLots.LotNo

Any help is appreciated
 
INSERT INTO StockOnHand ( prodIdentity, whsIdentity,
locIdentity, quantity, EffectiveDate, EffectiveTime,
ExpirationDate, LotNo )
Click to expand...

I don't believe that you can have a UNION query simultaneously being
an INSERT query.

Try creating and saving the UNION query, and then create a second
Append query *based on the stored UNION query*.

John W. Vinson[MVP]
(no longer chatting for now)
 
Back
Top