SQL Search within a table

veon cheng

Good day, I have some problem with my SQL Commands in access, really hope that you can help me out. Category

CatID Category Name Sub- Category Description

1 Beverages 123
2 Condiments 456
3 Juice 1 help
4 Coffee 1 pls

I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access. Really hope if any kind soul could help me with this Regards Veon

Corporal Agarn

Could you please show your SQL.

veon cheng

Sorry its kinda messy, its from access.. the bold part is the 1 have issue. once againg sorry

SELECT Product.[Electronic Product Code], Product.[Item Name], IIf([parent - category] Is Null,[name],[Category].[name]=[category].[Category ID].[name]) AS Category , IIf([Parent - Category] Is Not Null,[name],"") AS SubCategory, Location.Country, Location.Building, Location.[Street Address], [Tracking Record].Timestamp, [RFID Reader].[Reader ID]
FROM Location RIGHT JOIN ((([RFID Reader] RIGHT JOIN (Product LEFT JOIN [Tracking Record] ON Product.[Electronic Product Code] = [Tracking Record].[Electronic Product Code]) ON [RFID Reader].[Reader ID] = [Tracking Record].[RFID Reader ID]) LEFT JOIN Has ON Product.[Electronic Product Code] = Has.[Electronic Product Code]) LEFT JOIN Category ON Has.[Category ID] = Category.[Category ID]) ON (Location.Latitude = [Tracking Record].Latitude) AND (Location.Longtitude = [Tracking Record].Longtitude)
WHERE (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND ((Category.[Category ID])=[forms]![Search History]![cmbCat])) OR (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND (([forms]![Search History]![cmbCat]) Is Null)) OR (((Product.[Electronic Product Code]) Like "*" & [forms]![Search History]![tbxEPC] & "*") AND ((Product.[Item Name]) Like "*" & [forms]![Search History]![tbxProductName] & "*") AND (([forms]![Search History]![cmbCat]) Is Not Null) AND ((Category.[Parent - Category])=[forms]![Search History]![cmbCat]));</pre>

Ingo

veon cheng wrote:

I wanted to display the main category of the Juice which is beverage. No matter how hard i tried, i can't display the Main category in access.

SELECT a.CategoryName FROM Category a INNER JOIN Category b On a.CatId = b.SubId WHERE b.SubCategory = 'Juice';

------------------------------ Author of Primary ROleplaying SysTem How do I take my coffee? Black as midnight on a moonless night. War doesn't determine who's right. War determines who's left.