I need to run this query, and sum info so there is only a single cus_no. My current query gives me 14,000 records. I need it to be closer to 500.
Any ideas how to do this?
Code:
SELECT dbo_LINHST_SQL.cus_no, Sum(dbo_LINHST_SQL.qty_ordered) AS SumOfqty_ordered, dbo_iminvloc_sql.loc, dbo_CUSFIL_SQL.cus_name, dbo_LINHST_SQL.sls_amt, dbo_CUSFIL_SQL.terr, dbo_CUSFIL_SQL.search_name, dbo_iminvloc_sql.price AS MyPrice INTO tblSalesLastYear
FROM (dbo_CUSFIL_SQL INNER JOIN dbo_LINHST_SQL ON dbo_CUSFIL_SQL.cus_no = dbo_LINHST_SQL.cus_no) LEFT JOIN dbo_iminvloc_sql ON dbo_LINHST_SQL.item_no = dbo_iminvloc_sql.item_no
WHERE (((Mid(Str([billed_dt]),2,4))=2011) AND ((dbo_CUSFIL_SQL.slspsn_no)="30" Or (dbo_CUSFIL_SQL.slspsn_no)="31" Or (dbo_CUSFIL_SQL.slspsn_no)="32" Or (dbo_CUSFIL_SQL.slspsn_no)="33" Or (dbo_CUSFIL_SQL.slspsn_no)="34" Or (dbo_CUSFIL_SQL.slspsn_no)="99") AND ((dbo_CUSFIL_SQL.cus_type_cd) Not Like "NSINA"))
GROUP BY dbo_LINHST_SQL.cus_no, dbo_CUSFIL_SQL.slspsn_no, dbo_iminvloc_sql.loc, dbo_CUSFIL_SQL.cus_name, dbo_LINHST_SQL.sls_amt, dbo_CUSFIL_SQL.terr, dbo_CUSFIL_SQL.search_name, dbo_iminvloc_sql.price
HAVING (((dbo_iminvloc_sql.loc)="01") AND ((dbo_LINHST_SQL.sls_amt)>0) AND ((dbo_CUSFIL_SQL.terr) Not Like "OB"))
ORDER BY dbo_LINHST_SQL.cus_no, Sum(dbo_LINHST_SQL.qty_ordered) DESC , dbo_CUSFIL_SQL.slspsn_no;
Any ideas how to do this?