You may want to try GROUP BY client.id, client.company Instead of GROUP BY company
-----Original Message----- From: Eric J. Hoffman [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 3:50 PM To: CF-Talk Subject: Better Way to Query? Not getting good results from this: SELECT COUNT (customer_orders.corder_id) as TOTOT, client.company FROM customer_orders JOIN customer_orders_prod on customer_orders.corder_id = customer_orders_prod.order_id JOIN inventory on customer_orders_prod.product_id = inventory.service_id JOIN client on inventory.client_id = client.id WHERE customer_orders.orderdate BETWEEN #createODBCDate(start9)# AND #createODBCDate(DateAdd('D', 1, end9))# and corder_active = 1 GROUP BY company Each inventory item is tagged with a client ID. There is the order table, which is the summary, and the orders_prod table which is all the line items of inventory. My need is a report of orders by client_id. This query seems to give me more than if I just ran the total of orders. I only need to get one item from the order items to determine the client company. This shows me maybe 14,000+ for one client, when I know the total orders to be 12,000+. Thanks!! -------------------------------------------------------- Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: [EMAIL PROTECTED] www: http://www.ejhassociates.com tel: 651.717.4105 fax: 651.717.4115 mob: 651.245.2717 -------------------------------------------------------- This message contains confidential information and is intended only for [EMAIL PROTECTED] If you are not [email protected] you should not disseminate, distribute or copy this e-mail. Please notify [EMAIL PROTECTED] immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Eric J. Hoffman therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -------------------------------------------------------- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:239865 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
