All products not showing on Transactions for a particular date when there are no orders for it

[Louis]

I have a transactions table which captures data of all stock ins, sales and stock on hand at a particular time. However when I generate the reports on a data by date basis, only the products that have been stocked in or sold on that day show. Other products do not show. I have tried using outer joins but it still don't work. I need help, please. Thank you.

[CJ_London]

it will need an outer join but with no detail on your tables, relationships, the sql you have tried or an explanation of what 'does not work' means really don't see how we can help. Provide all 4 of the above then I'm sure we can point you in the right direction

[ArviLaanemets]

Let's assume, you have tables:
tblArticles: ArticleID, ArticleName, ...;
tblTransactions: TransactionID, TransactionType, TransactionDirection, TransactionDate, ArticleID, TransactionQty, ... (where TransactionType is smallint with values presenting e.g. purchases coming in, sending articles into production, getting articles from production, sending sold articles out, etc., and TransactionDirection is a smallint value either 1 when article is added to stock, or -1 whe article is removed from stock).

The query for your report may be (on fly) something like:

SELECT  bal.ArticleID. bal.ArticleName, InOut.StartDate, bal.BalanceDate AS EndDate, InOut.StockIn, InOut.StockOut, bal.ArticleBalance AS EndBalance
FROM
(SELECT art.ArticleID, art.ArticleName, [BalanceDate] As BalanceDate, SUM(tran1.TransactionDirection*tran1.TransactionQty) As ArticleBalance 
FROM tblArticles art LEFT JOIN tblTransactions tran1 ON tran1.ArticleID = art.ArticleID 
WHERE tran1.TransactionDate < [BalanceDate] GROUP BY art.ArticleID, art.ArticleName) As bal LEFT JOIN
(SELECT  tran2.ArtID, [PeriodStartDate] AS StartDate, SUM(Iif(tran2.TransactionDirection = 1, 1,0)*tran2.ArticleQty AS StockIn, SUM(Iif(tran2.TransactionDirection = -1, 1,0)*tran2.ArticleQty AS StockOut)
FROM tblTransactions tran2 WHERE tran2.TransactionDate > [PeriodStartDate] AND tran2.TraansactionDate < [BalanceDate] GROUP BY tran2.ArticleID) As InOut ON InOut.ArticleID = bal.ArticleID

[Louis]

Thanks ArvilLaanemets. This worked. Gracias.

Let's assume, you have tables:
tblArticles: ArticleID, ArticleName, ...;
tblTransactions: TransactionID, TransactionType, TransactionDirection, TransactionDate, ArticleID, TransactionQty, ... (where TransactionType is smallint with values presenting e.g. purchases coming in, sending articles into production, getting articles from production, sending sold articles out, etc., and TransactionDirection is a smallint value either 1 when article is added to stock, or -1 whe article is removed from stock).

The query for your report may be (on fly) something like:

SELECT  bal.ArticleID. bal.ArticleName, InOut.StartDate, bal.BalanceDate AS EndDate, InOut.StockIn, InOut.StockOut, bal.ArticleBalance AS EndBalance
FROM
(SELECT art.ArticleID, art.ArticleName, [BalanceDate] As BalanceDate, SUM(tran1.TransactionDirection*tran1.TransactionQty) As ArticleBalance 
FROM tblArticles art LEFT JOIN tblTransactions tran1 ON tran1.ArticleID = art.ArticleID 
WHERE tran1.TransactionDate < [BalanceDate] GROUP BY art.ArticleID, art.ArticleName) As bal LEFT JOIN
(SELECT  tran2.ArtID, [PeriodStartDate] AS StartDate, SUM(Iif(tran2.TransactionDirection = 1, 1,0)*tran2.ArticleQty AS StockIn, SUM(Iif(tran2.TransactionDirection = -1, 1,0)*tran2.ArticleQty AS StockOut)
FROM tblTransactions tran2 WHERE tran2.TransactionDate > [PeriodStartDate] AND tran2.TraansactionDate < [BalanceDate] GROUP BY tran2.ArticleID) As InOut ON InOut.ArticleID = bal.ArticleID