Calculations involve different table

[EMILYTAN via AccessMonster.com]

May I know how to perform calculations from different table?

ItemTable - Bil(primarykey),ItemNumber,Quantity

MainItemTable - ItemNumber(primary key)

Loan-LoanID(primary key)

LoanDetails-LoanID,ItemNumber,LoanQuantity

I wan to calculate the total quantity is Quantity from ItemTable minus
LoanQuantity from LoanDetails in Query........?
How?

 

[John Spencer]

SELECT I.ItemNumber, I.IQ-LD.LQ as Balance
FROM
(SELECT ItemTable.ItemNumber, SUM(ItemTable.Quantity) As IQ
FROM ItemTable
GROUP BY ItemNumber) as I
LEFT JOIN
(SELECT LoanDetails.ItemNumber, Sum(LoanDetails.LoanQuantity) as LQ
FROM LoanDetails
GROUP BY ItemNumber) as LD
ON I.ItemNumber = LD.ItemNumber

IF the above is too complex for you. Use three queries.
Query one saved as qItemQuantity would be a totals query that gets the
totals by item for the ItemTable.
Query two saved as qLoanQuantify would be a totals query that the the totals
by item for the LoanDetails table.

Query three would have the two saved queries as sources joined on the
ItemNumber fields and subtracting the two sums.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[EMILYTAN via AccessMonster.com]

Hi John,
I can get the 2 table to get the calculations only. I follow your
instructions already but still unable to get it. Perhaps, you might want to
have a look at my query

First query-> TotalQuantity
SecondQuery-> Quantity (in negative value)
Both the query are saved independantly.

I hope to create the third query with FinalQuantity and the formula is
TotalQuantity(first query)+Quantity(second query)

Thank You

SELECT I.ItemNumber, I.IQ-LD.LQ as Balance
FROM
(SELECT ItemTable.ItemNumber, SUM(ItemTable.Quantity) As IQ
FROM ItemTable
GROUP BY ItemNumber) as I
LEFT JOIN
(SELECT LoanDetails.ItemNumber, Sum(LoanDetails.LoanQuantity) as LQ
FROM LoanDetails
GROUP BY ItemNumber) as LD
ON I.ItemNumber = LD.ItemNumber

IF the above is too complex for you. Use three queries.
Query one saved as qItemQuantity would be a totals query that gets the
totals by item for the ItemTable.
Query two saved as qLoanQuantify would be a totals query that the the totals
by item for the LoanDetails table.

Query three would have the two saved queries as sources joined on the
ItemNumber fields and subtracting the two sums.

May I know how to perform calculations from different table?

[quoted text clipped - 9 lines]

LoanQuantity from LoanDetails in Query........?
How?

 

[John Spencer]

Please copy and post the SQL of your queries. IF possible I will look at
them and post a solution back to you. Please include the names of the saved
queries.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

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

Hi John,
I can get the 2 table to get the calculations only. I follow your
instructions already but still unable to get it. Perhaps, you might want
to
have a look at my query

First query-> TotalQuantity
SecondQuery-> Quantity (in negative value)
Both the query are saved independantly.

I hope to create the third query with FinalQuantity and the formula is
TotalQuantity(first query)+Quantity(second query)

Thank You

SELECT I.ItemNumber, I.IQ-LD.LQ as Balance
FROM
(SELECT ItemTable.ItemNumber, SUM(ItemTable.Quantity) As IQ
FROM ItemTable
GROUP BY ItemNumber) as I
LEFT JOIN
(SELECT LoanDetails.ItemNumber, Sum(LoanDetails.LoanQuantity) as LQ
FROM LoanDetails
GROUP BY ItemNumber) as LD
ON I.ItemNumber = LD.ItemNumber

IF the above is too complex for you. Use three queries.
Query one saved as qItemQuantity would be a totals query that gets the
totals by item for the ItemTable.
Query two saved as qLoanQuantify would be a totals query that the the
totals
by item for the LoanDetails table.

Query three would have the two saved queries as sources joined on the
ItemNumber fields and subtracting the two sums.

May I know how to perform calculations from different table?

[quoted text clipped - 9 lines]

LoanQuantity from LoanDetails in Query........?
How?

 

[EMILYTAN via AccessMonster.com]

Hi John,
Here are my SQL queries

1.TotalIn
SELECT PartNumber.PartNumber, Sum(PartNumberDetails.InQty) AS TotalIn, Sum
(PartNumberDetails.OutQty) AS TotalOut, [TotalIn]-[TotalOut]+[KanbanQuantity]
AS TotalQuantiy, PartNumber.KanbanQty AS KanbanQuantity
FROM PartNumber INNER JOIN PartNumberDetails ON PartNumber.
PartNumber=PartNumberDetails.PartNumber
GROUP BY PartNumber.PartNumber, PartNumber.KanbanQty
ORDER BY PartNumber.PartNumber;

2.LoanTracking
SELECT PartNumber.PartNumber, Sum(LoanDetails.QuantityLoan) AS QtyLoan, Sum
(LoanDetails.QuantityReturned) AS QtyReturn, [QtyReturn]-[QtyLoan] AS
Quantity
FROM PartNumber INNER JOIN (Loan INNER JOIN LoanDetails ON Loan.LoanID =
LoanDetails.LoanID) ON PartNumber.PartNumber = LoanDetails.PartNumber
GROUP BY PartNumber.PartNumber;

I hope to get the FinalQuantity which is the TotalQuantity(TotalIn query)+
Quantity(LoanTracking query)

Thank you..hopefully u can help me...

Please copy and post the SQL of your queries. IF possible I will look at
them and post a solution back to you. Please include the names of the saved
queries.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Hi John,
I can get the 2 table to get the calculations only. I follow your

[quoted text clipped - 36 lines]