CODEWITHSUNDEEP
sqljoinsubqueryms-access-2013
user2941143
user2941143

Reputation: 13

Access join of two subqueries

I am constructing a simple database for an inventory system, with a barcode reader. I have one form to add inventory, and one form to remove inventory. Each adjust add a column to the add or remove table.

I made queries for my add and remove that return the total value of each item, associated with the id for each item. I want to join my add and remove tables so I can calculate the difference for a current quantity.

I am running into trouble with joining my add subquery and my remove subquery.

Add:

SELECT Items.description, Count(Trans_Add.s) AS Quantity
FROM Items INNER JOIN Trans_Add ON Items.ID = Trans_Add.s
GROUP BY Items.description;

Remove:

SELECT Items.description, Count(Trans_Remove.s) AS Quantity
FROM Items INNER JOIN Trans_Remove ON Items.ID = Trans_Remove.s
GROUP BY Items.description;

Whenever I try to assign alias for either in the from clause of my new query and join them it does not work.

Upvotes: 1

Views: 7172

Answers (1)

Edper
Edper

Reputation: 9322

Try:

SELECT Items.ID, Items.Description, (Items.TotalCount + A.Quantity - B.Quantity) as OnHandQty 
FROM Items INNER JOIN 
(SELECT Items.ID, Count(Trans_Add.s) AS Quantity
FROM Items INNER JOIN Trans_Add ON Items.ID = Trans_Add.s
GROUP BY Items.description
) A
ON Items.ID = A.ID
INNER JOIN (
SELECT Items.description, Count(Trans_Remove.s) AS Quantity
FROM Items INNER JOIN Trans_Remove ON Items.ID = Trans_Remove.s
GROUP BY Items.description
) B
ON Items.ID = B.ID

I made up a Field named TotalCount because you did not mentioned what is the Item Quantity in Items table. So, change it accordingly, that is, according to the right field name for Item Quantity from Items table.

Upvotes: 3

Related Questions