I am trying to come up with a query to show the purchase and product information made by a member, and I was wondering if there is a way create a conditional statement to determine whether the product the member bought was from the Clothing table, Accessory table or if they bought a product from both tables. and the determining factor for which table is the ProductID in the Product table, if the user didn't but from the clothing table, the ProductID should be 0. The ProductID connects to both the Clothing and Accessory tables by their ProductTypeID, if you need any more information, let me know, thanks!
An image of the tables in Access is here
And here it is in SQL View
SELECT Member.MemberID, Member.FirstName, Member.LastName,
Purchase.PurchaseDate, LineItem.CalculatedPrice, Product.ProductType
FROM ClothingType
INNER JOIN ((AccessoryType INNER JOIN (((Member INNER JOIN Purchase
ON Member.MemberID = Purchase.MemberID)
INNER JOIN (Product INNER JOIN LineItem ON Product.ProductID = LineItem.ProductID)
ON Purchase.PurchaseID = LineItem.PurchaseID)
INNER JOIN Accessory ON Product.ProductID = Accessory.ProductTypeID)
ON AccessoryType.AccessoryTypeID = Accessory.AccessoryTypeID)
INNER JOIN Clothing ON Product.ProductID = Clothing.ProductTypeID)
ON ClothingType.ClothingTypeID = Clothing.ClothingTypeID;
you need to modify your query to use left joins. Unfortunately MS Access has its craziness about the parenthesis in the JOINS, so my query below may be erroneous. Please comment below if Access complains about errors in the JOIN clause, and I will do my best to fix it:
SELECT Member.MemberID, Member.FirstName, Member.LastName,
Purchase.PurchaseDate, LineItem.CalculatedPrice, Product.ProductType
FROM Member INNER JOIN (Purchase
INNER JOIN (Product INNER JOIN (LineItem
LEFT JOIN (Accessory LEFT JOIN (AccessoryType
LEFT JOIN (Clothing LEFT JOIN ClothingType ON
ClothingType.ClothingTypeID = Clothing.ClothingTypeID)
ON Product.ProductID = Clothing.ProductTypeID)
ON AccessoryType.AccessoryTypeID = Accessory.AccessoryTypeID)
ON Product.ProductID = Accessory.ProductTypeID)
ON Product.ProductID = LineItem.ProductID)
ON Purchase.PurchaseID = LineItem.PurchaseID)
ON Member.MemberID = Purchase.MemberID);
However, as I mentioned above, it may not work because of Access stupidity around its usage of JOINs. You may be better of to double-clicking on the last four join lines in the designer (to the right of Product) and make them all "Select all from the left table and only those that match from the right table" option (which is a LEFT JOIN in Access terms)
UPDATE: forgot to add: once you have your join working all you need to do is to use an expression for the Accessory/Clothing description like this:
Iif(IsNULL(AccessoryType.Description), ClothingType.Description, AccessoryType.Description) as Description