debug SQL Statement

The place to find the database for the SQL Statement is [login to view URL] There are 6 tables. The relationships between the tables are these: itemsown.inventory=[login to view URL] itemsold.inventory=[login to view URL] itemsold.invoice=[login to view URL] inventory.id=[login to view URL] exceptions.id=[login to view URL] exceptiondetails.exception=[login to view URL] Here is the statement I have so far which is almost correct but not quite. newdate=DateAdd("d", "-30", Now) sqls="SELECT [login to view URL], [login to view URL], [login to view URL], [login to view URL], Sum([login to view URL]) AS ItemsSold, [login to view URL], (SELECT Sum([login to view URL]) FROM itemsown WHERE [login to view URL] = [login to view URL]) AS ItemsHaveInStock" sqls=sqls & " FROM inventory INNER JOIN itemsold ON [login to view URL] = [login to view URL] where [login to view URL]>'" & newdate & "' AND itemsold.shouldhome='y'" sqls=sqls & " GROUP BY [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL] UNION ALL SELECT [login to view URL], [login to view URL], [login to view URL], [login to view URL], '', null, (SELECT Sum([login to view URL]) FROM itemsown" sqls=sqls & " WHERE [login to view URL] = [login to view URL]) AS ItemsHaveInStock FROM inventory WHERE NOT EXISTS (SELECT [login to view URL] FROM itemsold" sqls=sqls & " WHERE [login to view URL] = [login to view URL] AND [login to view URL]>'" & newdate & "' AND itemsold.shouldhome='y') ORDER BY [login to view URL]" The only problem is if a left side sells, but we have a right side, it shows the sell and shows we had the left side even though we did not. The example is part number 8-04. We sold a left side. There is no left side in stock. In this case, it should show 2 entries. One to show we sold a left side with none in stock and another one to show we sold 0 of the right side but have one in stock. Everything else is fine. What this statement does is shows what needs to be ordered. It is used with another formula to determine whether to show on the page (if number sold is greater than the number we have in stock). Below is information from the previous bid request. See deliverables for more information. ## Deliverables The completed SQL Statement to account for the side sold and the side I have in inventory. If there is no side, then everything is correct. Otherwise, if there is more than one side, show both. Even if its 0 sold and 0 in inventory for the statement. If the structure needs to be changed, that is fine as well. if an inventory item is in the itemsold table but nothing matching in the itemsown, then show it as null. The statement should return all the sales (count them) from the last 30 days. If nothing corresponds to the itemsown, then show that column as null. Also, from the itemsold, it should only show where shouldhome='y' The information retrieved should be something like this Inventory Number, Items Sold, Side, Items Currently Have For this sample, just show all the ones after 10/07/03. I can change it later to show for the last 30 days. The inventory number needs to be the [login to view URL] needs to group by [login to view URL] itemsold should count how many sold of the inventory item. items currently have should either be 0 if the current amount is 0. For the itemsown, no sum function is necessary. It needs to be grouped by side as well. It may or may not be null. The purpose of this is to show based on the 30 day sells, what items needs to be ordered. If there is one with a left and a right, show them twice. Let me know if you have any other questions. The statement needed will be similar but take into account of what side was sold and compared to what side I have in stock. ## Platform SQL Server 7.0, Access, Windows 2000