Show quantity for different table

[EMILYTAN via AccessMonster.com]

Below are my table:

Main Table
PartNumber(pk)

Incoming
Bil (pk)
PartNumber
Quantity


Loan
LoanID
PartNumber
Quantity


So, I need to have a query to show all the quantity as below structure. If
the part number quantity exists in incoming but not in kanban, it will show
kanban quantity 0
PartNumber Incoming Loan
1111-1111 10 12
2222-2222 10 5
3333-3333 10 0

 

[John Spencer]

The SQL for that would look like:

SELECT M.PartNumber, I.Quantity as IncomingQuantity, nz(L.Quantity,0) AS
LoanQuantity
FROM ([Main Table] as M INNER JOIN Incoming as I
ON M.PartNumber =I.PartNumber) LEFT JOIN Loan as L
ON I.PartNumber = L.PartNumber

In the query grid
-- Add all three tables
-- Join Main.PartNumber to Incoming.PartNumber
-- Join Incoming.PartNumber to Loan.PartNumber
-- Double click on the Incoming-Loan join line
---- Select All in Incoming and only matching in loan
-- Add the fields you want to see
-- for Loan.quantity use a calculated field
---- Field: LoanQuantity: NZ(Loan.Quantity,0)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[EMILYTAN via AccessMonster.com]

Thanks a lot John...
Although your solution does not really get what I want, but I am able to get
my answer after modifying a few steps....
Thanks ! You have gave me a great idea....thanks a lot..

The SQL for that would look like:

SELECT M.PartNumber, I.Quantity as IncomingQuantity, nz(L.Quantity,0) AS
LoanQuantity
FROM ([Main Table] as M INNER JOIN Incoming as I
ON M.PartNumber =I.PartNumber) LEFT JOIN Loan as L
ON I.PartNumber = L.PartNumber

In the query grid
-- Add all three tables
-- Join Main.PartNumber to Incoming.PartNumber
-- Join Incoming.PartNumber to Loan.PartNumber
-- Double click on the Incoming-Loan join line
---- Select All in Incoming and only matching in loan
-- Add the fields you want to see
-- for Loan.quantity use a calculated field
---- Field: LoanQuantity: NZ(Loan.Quantity,0)

Below are my table:

[quoted text clipped - 19 lines]

2222-2222 10 5
3333-3333 10 0