Hi, I believe I have the query in my recordset ALMOST working.
Please take a look:
SELECT inventory.itemId, inventory.itemName, inventory.itemDesc, inventory.itemPrice, inventory.itemQty, inventory.itemSize, inventory.itemColor, inventory.partNum, inventory.prodLine, orders.OrderDate, orderdetails.DetailItemId, orderdetails.DetailQuantity, SUM(orderdetails.DetailQuantity) AS OrderTotal, (OrderTotal*inventory.itemPrice) AS OrderQty, (inventory.itemQty*inventory.itemPrice) AS InventoryTotal
FROM orderdetails INNER JOIN inventory ON orderdetails.DetailItemId = inventory.itemId INNER JOIN orders ON orderdetails.DetailOrderId = orders.OrderId
GROUP BY orderdetails.DetailItemId, orders.OrderDate
ORDER BY itemName ASC
I need to first add together the values of the quantity sold for each of the individual items and have it appear in one field that DataAssist created. I then need to multiply the sum of that by the item price to come up with a currency usage of what was sold in a certain date range. I also need for what is currently available in inventory multiplied by the price of the item to appear in another field. I have all fields set up and appearing, however it is not grouping it properly. I need to group these by item id's and instead it is displaying each line item on a separate line without grouping them.
I really like WebAssist and all of it's products. The technical support and professionalism that is portrayed is superb, but if I could make one suggestion it would be to consider adding a more complex query builder to your DataAssist and/or search wizard.