Stuck on a Join

[kbreiss]

Hello,
I have two tables.....1.) Products, 2.) Inv_Transactions. I would like to write a query that includes all the Products even if there hasn't been a Transaction with that query. Here is the query I have know, but it does not include Products that have not had any transactions because the two tables do not link by Product_ID.

SELECT PRODUCTS.PROD_ID, PRODUCTS.PROD_NAME, Sum(INV_TRANSACTIONS.UNITS_ORDERED) AS ORDERED, Sum(INV_TRANSACTIONS.UNITS_RECEIVED) AS RECEIVED, Sum(INV_TRANSACTIONS.UNITS_DISTRIBUTED) AS DISTRIBUTED, Sum(UNITS_RECEIVED)-Sum(Units_Distributed) AS QNTY_ON_HAND, PRODUCTS.REORDER_LEVEL
FROM PRODUCTS INNER JOIN INV_TRANSACTIONS ON PRODUCTS.PROD_ID = INV_TRANSACTIONS.PROD_ID
WHERE (((INV_TRANSACTIONS.DATE)>=[forms]![Report Date Range]![BeginDate] And (INV_TRANSACTIONS.DATE)<=[forms]![Report Date Range]![EndDate]))
GROUP BY PRODUCTS.PROD_ID, PRODUCTS.PROD_NAME, PRODUCTS.REORDER_LEVEL;





Hopefully somebody has some suggestions.
Thanks in advance,

Kacy
________
Teen shower

 

[FoFa]

Try this:
SELECT PRODUCTS.PROD_ID, PRODUCTS.PROD_NAME, Sum(INV_TRANSACTIONS.UNITS_ORDERED) AS ORDERED, Sum(INV_TRANSACTIONS.UNITS_RECEIVED) AS RECEIVED, Sum(INV_TRANSACTIONS.UNITS_DISTRIBUTED) AS DISTRIBUTED, Sum(UNITS_RECEIVED)-Sum(Units_Distributed) AS QNTY_ON_HAND, PRODUCTS.REORDER_LEVEL
FROM PRODUCTS LEFT JOIN INV_TRANSACTIONS ON PRODUCTS.PROD_ID = INV_TRANSACTIONS.PROD_ID
WHERE ((INV_TRANSACTIONS.DATE>=[forms]![Report Date Range]![BeginDate] And INV_TRANSACTIONS.DATE<=[forms]![Report Date Range]![EndDate]) OR (INV_TRANSACTIONS.DATE IS NULL))
GROUP BY PRODUCTS.PROD_ID, PRODUCTS.PROD_NAME, PRODUCTS.REORDER_LEVEL;