Conditional statement to determine which table to Select from in Access 2013

Question

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;

It would be better if you could switch to SQL view and copy-n-paste the SQL Statement here — cha, Dec 03 '14 at 22:39
I would suggest a redesign of the database, is there any reason an accessory should not be listed in a combined clothing-accessory table with an extra field / column to indicate item type? I am a little surprised that the details are not included in Products. — Fionnuala, Dec 04 '14 at 00:12

score 0 · Accepted Answer · answered Dec 03 '14 at 23:24

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

Yeah Access complained on the Joins, you can attempt to fix it, I'll try converting it to a more Access-friendly format as well, let me know if you decide to take on that challenge, thanks! — Jerryq27, Dec 04 '14 at 04:56
have you tried to use the GUI? Believe me it is much easier that way. — cha, Dec 04 '14 at 05:05

Conditional statement to determine which table to Select from in Access 2013

1 Answers1