Show quantity for different table

  • Thread starter EMILYTAN via AccessMonster.com
  • Start date

E

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
 
Ad

Advertisements

J

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
..
 
Ad

Advertisements

E

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..

John said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top