search description txtbox not showing all items (1 Viewer)

[mabino79]

SELECT tblInventoryTransaction.ProductCode, Sum(tblInventoryTransaction.ActualQty) AS SumOfActualQty, tblTransactionType.TransactionType, tblInventoryTransaction.Ref_no, tblInventoryTransaction.DateofTransaction, tblInventoryTransaction.ProductCode, tblInventoryTransaction.Qty, tblInventoryTransaction.EmployeeSales, tblInventoryTransaction.TransactionType, tblInventoryTransaction.ActualQty, tblInventoryTransaction.Project, tblInventoryTransaction.Remarks, tblInventoryTransaction.ProductCode, tblInventory.Description
FROM tblTransactionType INNER JOIN (tblInventory INNER JOIN tblInventoryTransaction ON tblInventory.ItemID = tblInventoryTransaction.ProductCode) ON tblTransactionType.TransactiontypeID = tblInventoryTransaction.TransactionType
GROUP BY tblTransactionType.TransactionType, tblInventoryTransaction.Ref_no, tblInventoryTransaction.DateofTransaction, tblInventoryTransaction.Qty, tblInventoryTransaction.EmployeeSales, tblInventoryTransaction.TransactionType, tblInventoryTransaction.ActualQty, tblInventoryTransaction.Project, tblInventoryTransaction.Remarks, tblInventoryTransaction.ProductCode, tblInventory.Description, tblInventoryTransaction.ProductCode
HAVING (((tblInventory.Description) Like "*" & ([Forms]![frmSearchProducts]![txtSearch]) & "*"));

have created a Form to Search Prodcut description, but not all products are showing in Query result.

please suggest where is going wrong

 

[theDBguy]

Since you're using an inner join, those products that match your description but don't have matching transactions won't be included in the result. Also, you should probably use a WHERE clause instead of a HAVING clause.

 

[The_Doc_Man]

First thing that comes to mind is that with a doubled inner join, you have three fields that have to match, so if there are any unmatched items in one of the lists, there is where you would lose some records.

Then, there is the HAVING clause that requires a "LIKE" relationship exists to your search box. Not to mention that it shouldn't be a "HAVING" clause anyway, since the thing you are having isn't an aggregated item. A WHERE clause might reduce the time this query takes to run because a HAVING has to wait for all joining, selection, and grouping to occur, whereas a WHERE can take effect earlier to reduce the size of the data set.

 

[mabino79]

can you help with rewrite the code as i have done in Query Design not familiear with SQL Codes

 

[mabino79]

SELECT tblInventoryTransaction.ProductCode, Sum(tblInventoryTransaction.ActualQty) AS SumOfActualQty, tblTransactionType.TransactionType, tblInventoryTransaction.Ref_no, tblInventoryTransaction.DateofTransaction, tblInventoryTransaction.ProductCode, tblInventoryTransaction.Qty, tblInventoryTransaction.EmployeeSales, tblInventoryTransaction.TransactionType, tblInventoryTransaction.ActualQty, tblInventoryTransaction.Project, tblInventoryTransaction.Remarks, tblInventoryTransaction.ProductCode
FROM tblTransactionType INNER JOIN (tblInventory INNER JOIN tblInventoryTransaction ON tblInventory.ItemID = tblInventoryTransaction.ProductCode) ON tblTransactionType.TransactiontypeID = tblInventoryTransaction.TransactionType
WHERE (((tblInventory.Description) Like "*" & ([Forms]![frmSearchProducts]![txtSearch]) & "*"))
GROUP BY tblTransactionType.TransactionType, tblInventoryTransaction.Ref_no, tblInventoryTransaction.DateofTransaction, tblInventoryTransaction.Qty, tblInventoryTransaction.EmployeeSales, tblInventoryTransaction.TransactionType, tblInventoryTransaction.ActualQty, tblInventoryTransaction.Project, tblInventoryTransaction.Remarks, tblInventoryTransaction.ProductCode, tblInventoryTransaction.ProductCode;


still the same, not showing all results

 

[theDBguy]

Again, that's as expected if you keep using inner joins. If you want to include all matching inventory descriptions, you'll have to use an outer join. Besides, I don't see you displaying any columns from the inventory table, any matching descriptions without corresponding transactions will just show up as blank rows in your query.

 

[mabino79]

i have a product table & a transaction table, in the query i need the Transactions with the Description Critiria to show.

 

[theDBguy]

View attachment 97826

i have a product table & a transaction table, in the query i need the Transactions with the Description Critiria to show.

Again, you could use an outer join to include every description, but if you're not selecting the description column, meaning you're only selecting fields from the transactions table, then those rows will just show up empty in your query.

First of all, could you please verify that the missing descriptions from your query actually have matching records in your transactions table?

 

[mabino79]

yes, thats what i need to do.

to show the transactions matching textbox description from the Products matching only.

 

[theDBguy]

yes, thats what i need to do.

to show the transactions matching textbox description from the Products matching only.

Not sure we're on the same page. The way I understand your table structure, the descriptions are not stored in the transactions table. Instead, the descriptions are stored in the inventory table. Isn't that correct? If so, if there are inventories without transactions, would you still like to see them in your query? If so, you will need to use an outer join and also select some columns from the inventory table. Otherwise, you'll get blank rows. They won't be missing anymore, but show up empty.