I have a table (Inventory_Line) where LID is auto increment and IID is a single number referring to the inventory date, PID is a numeric part#. We use this table for inventory.

`LID IID NAME PID QTY --- --- ------ --- --- 1 1 Part A 213 12 2 1 Part B 200 15 3 2 Part A 213 9 4 2 Part B 200 7 `

We also have a table Order_Line

`OLID OID NAME PID QTY ---- --- ------ --- --- 1 217 Part A 213 12 2 217 Part B 200 15 3 218 Part A 213 9 4 218 Part B 200 7 `

My goal is to show

`((Previous Inventory Qty (Inventory_Line.IID=1)) AS PREV_INV + (ORDERED Qty (Order_Line.OID = 217 AND 218)) AS ORDERED - (Current Inventory Qty Inventory_Line.IID=2) AS CURRENT_INV) AS SOLD WHERE PID = X `

The output would be like this:

`PID NAME PREV_INV ORDERED CURRENT_INV SOLD --- ------ -------- ------- ----------- ---- 213 Part A 12 21 9 24 `

We are using MS Access and I have some experience with Joins but I am kind of stuck on how to pull this off. Any help would be appreciated.

-------------Problems Reply------------

Are you thinking of something on these lines?

`SELECT t.pid,`

Nz([pi].[qty], 0) + Nz([o].[qty], 0) - Nz([s].[qty], 0) AS dat

FROM (((SELECT DISTINCT pid

FROM inventory_line) AS t

LEFT JOIN (SELECT *

FROM inventory_line

WHERE iid = 2) AS pi

ON t.pid = pi.pid)

LEFT JOIN (SELECT *

FROM inventory_line

WHERE iid = 3) AS s

ON t.pid = s.pid)

LEFT JOIN (SELECT *

FROM order_line

WHERE oid = 217

OR oid = 218) AS o

ON t.pid = o.pid

WHERE (( ( t.pid ) = [Enter:] ))

The above needs some work, but what should be done would be clearer with more input on the desired output.

Re comments

`SELECT t.pid,`

t.name,

pi.qty,

o.q,

s.qty,

Nz([pi].[qty]) + Nz([o].[q]) - Nz([s].[Qty]) AS sold

FROM (((SELECT DISTINCT pid,

[Name]

FROM inventory_line) AS t

LEFT JOIN (SELECT pid,

qty

FROM inventory_line

WHERE iid = 1) AS pi

ON t.pid = pi.pid)

LEFT JOIN (SELECT pid,

qty

FROM inventory_line

WHERE iid = 2) AS s

ON t.pid = s.pid)

LEFT JOIN (SELECT pid,

SUM(qty) AS q

FROM order_line

WHERE oid = 217

OR oid = 218

GROUP BY pid) AS o

ON t.pid = o.pid

WHERE (( ( t.pid ) = [Enter:] ))

I couldn't work out the where the 24 in the SOLD column comes from but here is another solution for the rest

`SELECT Order_Line.PID, Order_Line.NAME, DLookUp("[QTY]","[Order_Line]","[OID]=" & [OID]-1 & " AND PID=" & [PID]) AS PREV_INV, DSum("[QTY]","[Order_Line]","[PID]=" & [PID]) AS ORDERED, Order_Line.QTY AS CURRENT_INV FROM Order_Line;`