Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With MS Query Sum Distinct
Hi everyone.
I hope someone can help me out with a SQL formula I am using with MS Query in Excel. This is what I have right now: SELECT DISTINCT v_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS 'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST') What I need help with is adjusting this SQL command so that I get rid ciSE.ciSEID AS 'Transaction ID' but still maintain the same results. I used the ciSE.ciSEID field because it was used to filter out duplicates. However when I take this field out and use the Distinct, my sum includes all the duplicates. My goal is to have the sum of ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which will bring over fewer rows. I would greatly appreciate any suggestions and help on this. Thank you in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With MS Query Sum Distinct
People forget the SQL's are long strings try this SelectSQL = _ "SELECT DISTINCT v_inputfiles.sName AS 'Client Name'," & _ "ciSE.ciSEqty," & _ "ciSE.ciSEvalue," & _ "ciSE.ciSECharge," & _ "ciSE.ciSEInRefSource," & _ "ciSE.ciSEInRefData" FromSQL = _ "FROM chronosv2.dbo.ciSE ciSE," & _ "chronosv2.dbo.v_inputfiles v_inputfiles" WhereSQL = _ "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ "((ciSE.ciSEbillingmonth='2008-10'))" GroupSQL = _ "GROUP BY v_inputfiles.sName, " & _ "ciSE.ciSEID, " & _ "ciSE.ciSEqty, " & _ "ciSE.ciSEvalue, " & _ "ciSE.ciSECharge, " & _ "ciSE.ciSEInRefSource," & _ "ciSE.ciSEInRefData" HavingSQL = _ "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')" Sql = SelectSQL & FromSQL & WhereSQL & HavingSQL "Lexster" wrote: Hi everyone. I hope someone can help me out with a SQL formula I am using with MS Query in Excel. This is what I have right now: SELECT DISTINCT v_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS 'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST') What I need help with is adjusting this SQL command so that I get rid ciSE.ciSEID AS 'Transaction ID' but still maintain the same results. I used the ciSE.ciSEID field because it was used to filter out duplicates. However when I take this field out and use the Distinct, my sum includes all the duplicates. My goal is to have the sum of ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which will bring over fewer rows. I would greatly appreciate any suggestions and help on this. Thank you in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With MS Query Sum Distinct
Thank you so much for responding.
Unfortunately, your suggestion did not work. I get an error message "Incorrect Syntax near '&'. Statement(s) could not be prepared." I have modified my top statement as: SELECT v_inputfiles.sName AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName However I still get more rows listed from the v_inputfiles.Sname than I want. Another dilemma is that when I try to sum Quanty, Value, and Charge, the total will include duplicates even though I am trying to rid duplicates in my Where IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) statement. This is the query I tried to use - it returns summary lines for v_inputfiles.name (which is what I want) but it includes duplicates in the totals for Quantity, value, Charge: SELECT v_inputfiles.sName , SUM (ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Value', SUM (ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') I am using MS Query in Excel. Thanks again for responding... you're the only one so far. All the best, Lex On Mar 5, 1:03*pm, Joel wrote: People forget the SQL's are long strings *try this SelectSQL = _ * *"SELECTDISTINCTv_inputfiles.sName AS 'Client Name'," & _ * *"ciSE.ciSEqty," & _ * *"ciSE.ciSEvalue," & _ * *"ciSE.ciSECharge," & _ * *"ciSE.ciSEInRefSource," & _ * *"ciSE.ciSEInRefData" FromSQL = _ * *"FROM chronosv2.dbo.ciSE ciSE," & _ * *"chronosv2.dbo.v_inputfiles v_inputfiles" WhereSQL = _ * *"WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ * *"((ciSE.ciSEbillingmonth='2008-10'))" GroupSQL = _ * *"GROUP BY v_inputfiles.sName, " & _ * *"ciSE.ciSEID, " & _ * *"ciSE.ciSEqty, " & _ * *"ciSE.ciSEvalue, " & _ * *"ciSE.ciSECharge, " & _ * *"ciSE.ciSEInRefSource," & _ * *"ciSE.ciSEInRefData" HavingSQL = _ * *"HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')" Sql = SelectSQL & FromSQL & WhereSQL & HavingSQL "Lexster" wrote: Hi everyone. I hope someone can help me out with a SQL formula I am using with MS Query in Excel. This is what I have right now: SELECTDISTINCTv_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS 'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST') What I need help with is adjusting this SQL command so that I get rid ciSE.ciSEID AS 'Transaction ID' but still maintain the same results. I used the ciSE.ciSEID field because it was used to filter out duplicates. However when I take this field out and use theDistinct, mysumincludes all the duplicates. My goal is to have thesumof ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which will bring over fewer rows. I would greatly appreciate any suggestions and help on this. Thank you in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With MS Query Sum Distinct
I still think you have to isolate the problem by eliminating some of the SQL.
I think my previous code had a problem because I left out spaces between Select, From, Where, and Group. I also suspect that moving the SUM to the end of the SQL will eliminate the duplicates. hre is the code I modified using you latest SQL and putting in the missing spaces (at the end of From, where, group) SelectSQL = "SELECT v_inputfiles.sName ," & _ "ciSE.ciSEInRefSource, " & _ "ciSE.ciSEInRefData " FromSQL = "FROM chronosv2.dbo.ciSE ciSE, " & _ "chronosv2.dbo.v_inputfiles v_inputfiles " WhereSQL = "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ "((ciSE.ciSEbillingmonth='2008-10')) AND " & _ "ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) " GroupSQL = "GROUP BY v_inputfiles.sName, " & _ "ciSE.ciSEInRefSource, " & _ "ciSE.ciSEInRefData " HavingSQL = "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') " SumSQL = "SUM (ciSE.ciSEqty) AS 'Quantity', " & _ "SUM (ciSE.ciSEvalue) AS 'Value', " & _ "SUM (ciSE.ciSECharge) AS 'Charge' " Sql = SelectSQL & FromSQL & WhereSQL & GroupSQL & HavingSQL & SumSQL "Lexster" wrote: Thank you so much for responding. Unfortunately, your suggestion did not work. I get an error message "Incorrect Syntax near '&'. Statement(s) could not be prepared." I have modified my top statement as: SELECT v_inputfiles.sName AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName However I still get more rows listed from the v_inputfiles.Sname than I want. Another dilemma is that when I try to sum Quanty, Value, and Charge, the total will include duplicates even though I am trying to rid duplicates in my Where IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) statement. This is the query I tried to use - it returns summary lines for v_inputfiles.name (which is what I want) but it includes duplicates in the totals for Quantity, value, Charge: SELECT v_inputfiles.sName , SUM (ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Value', SUM (ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') I am using MS Query in Excel. Thanks again for responding... you're the only one so far. All the best, Lex On Mar 5, 1:03 pm, Joel wrote: People forget the SQL's are long strings try this SelectSQL = _ "SELECTDISTINCTv_inputfiles.sName AS 'Client Name'," & _ "ciSE.ciSEqty," & _ "ciSE.ciSEvalue," & _ "ciSE.ciSECharge," & _ "ciSE.ciSEInRefSource," & _ "ciSE.ciSEInRefData" FromSQL = _ "FROM chronosv2.dbo.ciSE ciSE," & _ "chronosv2.dbo.v_inputfiles v_inputfiles" WhereSQL = _ "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ "((ciSE.ciSEbillingmonth='2008-10'))" GroupSQL = _ "GROUP BY v_inputfiles.sName, " & _ "ciSE.ciSEID, " & _ "ciSE.ciSEqty, " & _ "ciSE.ciSEvalue, " & _ "ciSE.ciSECharge, " & _ "ciSE.ciSEInRefSource," & _ "ciSE.ciSEInRefData" HavingSQL = _ "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')" Sql = SelectSQL & FromSQL & WhereSQL & HavingSQL "Lexster" wrote: Hi everyone. I hope someone can help me out with a SQL formula I am using with MS Query in Excel. This is what I have right now: SELECTDISTINCTv_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS 'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST') What I need help with is adjusting this SQL command so that I get rid ciSE.ciSEID AS 'Transaction ID' but still maintain the same results. I used the ciSE.ciSEID field because it was used to filter out duplicates. However when I take this field out and use theDistinct, mysumincludes all the duplicates. My goal is to have thesumof ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which will bring over fewer rows. I would greatly appreciate any suggestions and help on this. Thank you in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With MS Query Sum Distinct
Thanks Joel.
Here to help clarify what I am trying to do... sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37769990 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST Client 1 37770035 2 40 2.8 TM ELIST Client 1 37770036 2 40 2.8 TM ELIST Client 1 37770046 2 40 2.8 TM FB08SPLASH Client 1 37770074 4 80 5.6 TM ELIST Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST 28 704 49.28 When I run this query (I have modified my original query): SELECT v_inputfiles.sName AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName This query takes out the duplicates in ciSEID - you will notice there are two lines missing. sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37769990 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST Client 1 37770035 2 40 2.8 TM ELIST Client 1 37770036 2 40 2.8 TM ELIST Client 1 37770046 2 40 2.8 TM FB08SPLASH Client 1 37770074 4 80 5.6 TM ELIST 20 472 33.04 I use the following query: SELECT v_inputfiles.sName , SUM (ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Quantity', SUM (ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') This includes the duplicates in the Sum totals. sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 8 160 11.2 TM FB08SPLASH Client 1 20 544 38.8 TM ELIST 28 704 49.28 Notice the totals add up to the same as the first example. My end goal is to get this: sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 6 120 8.4 TM FB08SPLASH Client 1 14 352 24.6 TM ELIST 20 472 33.04 Fewer lines and removing duplicates from the totals. On Mar 9, 11:07*am, Joel wrote: I still think you have to isolate the problem by eliminating some of the SQL. *I think my previous code had a problem because I left out spaces between Select, From, Where, and Group. *I also suspect that moving theSUMto the end of the SQL will eliminate the duplicates. *hre is the code I modified using you latest SQL and putting in the missing spaces (at the end of From, where, group) SelectSQL = "SELECT v_inputfiles.sName ," & _ * *"ciSE.ciSEInRefSource, " & _ * *"ciSE.ciSEInRefData " FromSQL = "FROM chronosv2.dbo.ciSE ciSE, " & _ * *"chronosv2.dbo.v_inputfiles v_inputfiles " WhereSQL = "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ * *"((ciSE.ciSEbillingmonth='2008-10')) AND " & _ * *"ciSE.ciSEID IN ( SELECTDISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) " GroupSQL = "GROUP BY v_inputfiles.sName, " & _ * *"ciSE.ciSEInRefSource, " & _ * *"ciSE.ciSEInRefData " HavingSQL = "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') " SumSQL = "SUM(ciSE.ciSEqty) AS 'Quantity', " & _ * *"SUM(ciSE.ciSEvalue) AS 'Value', " & _ * *"SUM(ciSE.ciSECharge) AS 'Charge' " Sql = SelectSQL & FromSQL & WhereSQL & GroupSQL & HavingSQL & SumSQL "Lexster" wrote: Thank you so much for responding. Unfortunately, your suggestion did not work. *I get an error message "Incorrect Syntax near '&'. Statement(s) could not be prepared." I have modified my top statement as: SELECT v_inputfiles.sName *AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName However I still get more rows listed from the v_inputfiles.Sname than I want. *Another dilemma is that when I try tosumQuanty, Value, and Charge, the total will include duplicates even though I am trying to rid duplicates in my Where IN ( SELECTDISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) statement. This is the query I tried to use - it returns summary lines for v_inputfiles.name (which is what I want) but it includes duplicates in the totals for Quantity, value, Charge: SELECT v_inputfiles.sName ,SUM(ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Value',SUM(ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') I am using MS Query in Excel. Thanks again for responding... you're the only one so far. All the best, Lex On Mar 5, 1:03 pm, Joel wrote: People forget the SQL's are long strings *try this SelectSQL = _ * *"SELECTDISTINCTv_inputfiles.sName AS 'Client Name'," & _ * *"ciSE.ciSEqty," & _ * *"ciSE.ciSEvalue," & _ * *"ciSE.ciSECharge," & _ * *"ciSE.ciSEInRefSource," & _ * *"ciSE.ciSEInRefData" FromSQL = _ * *"FROM chronosv2.dbo.ciSE ciSE," & _ * *"chronosv2.dbo.v_inputfiles v_inputfiles" WhereSQL = _ * *"WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ * *"((ciSE.ciSEbillingmonth='2008-10'))" GroupSQL = _ * *"GROUP BY v_inputfiles.sName, " & _ * *"ciSE.ciSEID, " & _ * *"ciSE.ciSEqty, " & _ * *"ciSE.ciSEvalue, " & _ * *"ciSE.ciSECharge, " & _ * *"ciSE.ciSEInRefSource," & _ * *"ciSE.ciSEInRefData" HavingSQL = _ * *"HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')" Sql = SelectSQL & FromSQL & WhereSQL & HavingSQL "Lexster" wrote: Hi everyone. I hope someone can help me out with a SQL formula I am using with MS Query in Excel. This is what I have right now: SELECTDISTINCTv_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS 'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST') What I need help with is adjusting this SQL command so that I get rid ciSE.ciSEID AS 'Transaction ID' but still maintain the same results.. I used the ciSE.ciSEID field because it was used to filter out duplicates. However when I take this field out and use theDistinct, mysumincludes all the duplicates. My goal is to have thesumof ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which will bring over fewer rows. I would greatly appreciate any suggestions and help on this. Thank you in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With MS Query Sum Distinct
Thank you Joel-
Here to help clarify what I am trying to do... sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37769990 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST Client 1 37770035 2 40 2.8 TM ELIST Client 1 37770036 2 40 2.8 TM ELIST Client 1 37770046 2 40 2.8 TM FB08SPLASH Client 1 37770074 4 80 5.6 TM ELIST Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST 28 704 49.28 When I run this query (I have modified my original query): SELECT v_inputfiles.sName AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName This query takes out the duplicates in ciSEID - you will notice there are two lines missing. sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37769990 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST Client 1 37770035 2 40 2.8 TM ELIST Client 1 37770036 2 40 2.8 TM ELIST Client 1 37770046 2 40 2.8 TM FB08SPLASH Client 1 37770074 4 80 5.6 TM ELIST 20 472 33.04 I use the following query: SELECT v_inputfiles.sName , SUM (ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Quantity', SUM (ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') This includes the duplicates in the Sum totals. sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 8 160 11.2 TM FB08SPLASH Client 1 20 544 38.08 TM ELIST 28 704 49.28 Notice the totals add up to the same as the first example. My end goal is to get this: sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 6 120 8.4 TM FB08SPLASH Client 1 14 352 24.64 TM ELIST 20 472 33.04 Fewer lines and removing duplicates from the totals. On Mar 9, 11:07*am, Joel wrote: I still think you have to isolate the problem by eliminating some of the SQL. *I think my previous code had a problem because I left out spaces between Select, From, Where, and Group. *I also suspect that moving theSUMto the end of the SQL will eliminate the duplicates. *hre is the code I modified using you latest SQL and putting in the missing spaces (at the end of From, where, group) SelectSQL = "SELECT v_inputfiles.sName ," & _ * *"ciSE.ciSEInRefSource, " & _ * *"ciSE.ciSEInRefData " FromSQL = "FROM chronosv2.dbo.ciSE ciSE, " & _ * *"chronosv2.dbo.v_inputfiles v_inputfiles " WhereSQL = "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ * *"((ciSE.ciSEbillingmonth='2008-10')) AND " & _ * *"ciSE.ciSEID IN ( SELECTDISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) " GroupSQL = "GROUP BY v_inputfiles.sName, " & _ * *"ciSE.ciSEInRefSource, " & _ * *"ciSE.ciSEInRefData " HavingSQL = "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') " SumSQL = "SUM(ciSE.ciSEqty) AS 'Quantity', " & _ * *"SUM(ciSE.ciSEvalue) AS 'Value', " & _ * *"SUM(ciSE.ciSECharge) AS 'Charge' " Sql = SelectSQL & FromSQL & WhereSQL & GroupSQL & HavingSQL & SumSQL "Lexster" wrote: Thank you so much for responding. Unfortunately, your suggestion did not work. *I get an error message "Incorrect Syntax near '&'. Statement(s) could not be prepared." I have modified my top statement as: SELECT v_inputfiles.sName *AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName However I still get more rows listed from the v_inputfiles.Sname than I want. *Another dilemma is that when I try tosumQuanty, Value, and Charge, the total will include duplicates even though I am trying to rid duplicates in my Where IN ( SELECTDISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) statement. This is the query I tried to use - it returns summary lines for v_inputfiles.name (which is what I want) but it includes duplicates in the totals for Quantity, value, Charge: SELECT v_inputfiles.sName ,SUM(ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Value',SUM(ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') I am using MS Query in Excel. Thanks again for responding... you're the only one so far. All the best, Lex On Mar 5, 1:03 pm, Joel wrote: People forget the SQL's are long strings *try this SelectSQL = _ * *"SELECTDISTINCTv_inputfiles.sName AS 'Client Name'," & _ * *"ciSE.ciSEqty," & _ * *"ciSE.ciSEvalue," & _ * *"ciSE.ciSECharge," & _ * *"ciSE.ciSEInRefSource," & _ * *"ciSE.ciSEInRefData" FromSQL = _ * *"FROM chronosv2.dbo.ciSE ciSE," & _ * *"chronosv2.dbo.v_inputfiles v_inputfiles" WhereSQL = _ * *"WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ * *"((ciSE.ciSEbillingmonth='2008-10'))" GroupSQL = _ * *"GROUP BY v_inputfiles.sName, " & _ * *"ciSE.ciSEID, " & _ * *"ciSE.ciSEqty, " & _ * *"ciSE.ciSEvalue, " & _ * *"ciSE.ciSECharge, " & _ * *"ciSE.ciSEInRefSource," & _ * *"ciSE.ciSEInRefData" HavingSQL = _ * *"HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')" Sql = SelectSQL & FromSQL & WhereSQL & HavingSQL "Lexster" wrote: Hi everyone. I hope someone can help me out with a SQL formula I am using with MS Query in Excel. This is what I have right now: SELECTDISTINCTv_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS 'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST') What I need help with is adjusting this SQL command so that I get rid ciSE.ciSEID AS 'Transaction ID' but still maintain the same results.. I used the ciSE.ciSEID field because it was used to filter out duplicates. However when I take this field out and use theDistinct, mysumincludes all the duplicates. My goal is to have thesumof ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which will bring over fewer rows. I would greatly appreciate any suggestions and help on this. Thank you in advance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help With MS Query Sum Distinct
I'm much better with Exel than SQL. Here is my solution combining your code
with my excel. Sub GetQuery() ' ' With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;" & _ "DBQ=C:\TEMP\test\db1.mdb;" & _ "FIL=MS Access;" & _ "PageTimeou")), Destination:=Range("A1")) SelectSQL = "SELECT " & _ "ciSE.sName AS 'Client Name', " & _ "ciSE.ciSEID, " & _ "ciSE.ciSEqty AS 'Quantity', " & _ "ciSE.ciSEvalue AS 'Value', " & _ "ciSE.ciSECharge AS 'Charge', " & _ "ciSE.ciSEInRefSource AS 'Source', " & _ "ciSE.ciSEInRefData AS 'Program', " & _ "SUM(ciSE.ciSEqty) AS 'Sum of ciSEqty', " & _ "SUM(ciSE.ciSEvalue) AS 'Quant', " & _ "SUM (ciSE.ciSECharge) AS 'Ch' " FromSQL = "FROM `C:\TEMP\test\db1`.ciSE ciSE" WhereSQL = "WHERE ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM ciSE) " GroupSQL = "GROUP BY " & _ "ciSE.sName, " & _ "ciSE.ciSEID, " & _ "ciSE.ciSEqty, " & _ "ciSE.ciSEvalue, " & _ "ciSE.ciSECharge, " & _ "ciSE.ciSEInRefSource, " & _ "ciSE.ciSEInRefData " HavingSQL = "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') " Sql = SelectSQL & Chr(13) & Chr(10) & _ FromSQL & Chr(13) & Chr(10) & _ WhereSQL & Chr(13) & Chr(10) & _ GroupSQL & Chr(13) & Chr(10) & _ HavingSQL & Chr(13) & Chr(10) .CommandText = Sql .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .BackgroundQuery = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Set oldsht = ActiveSheet oldsht.Copy after:=Sheets(Sheets.Count) ActiveSheet.Name = "Summary" With Sheets("Summary") 'delete ID colun .Columns("B").Delete LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set SortRange = .Rows("1:" & LastRow) SortRange.Sort _ key1:=.Range("A1"), _ order1:=xlAscending, _ key2:=.Range("E1"), _ order2:=xlAscending, _ key3:=.Range("F1"), _ order3:=xlAscending, _ header:=xlYes RowCount = 2 Do While .Range("A" & RowCount) < "" If .Range("E" & RowCount) = .Range("E" & (RowCount + 1)) And _ .Range("F" & RowCount) = .Range("F" & (RowCount + 1)) Then .Range("B" & RowCount) = .Range("B" & RowCount) + _ .Range("B" & (RowCount + 1)) .Range("C" & RowCount) = .Range("C" & RowCount) + _ .Range("C" & (RowCount + 1)) .Range("D" & RowCount) = .Range("D" & RowCount) + _ .Range("D" & (RowCount + 1)) .Rows(RowCount + 1).Delete Else RowCount = RowCount + 1 End If Loop End With End Sub "Lexster" wrote: Thank you Joel- Here to help clarify what I am trying to do... sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37769990 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST Client 1 37770035 2 40 2.8 TM ELIST Client 1 37770036 2 40 2.8 TM ELIST Client 1 37770046 2 40 2.8 TM FB08SPLASH Client 1 37770074 4 80 5.6 TM ELIST Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST 28 704 49.28 When I run this query (I have modified my original query): SELECT v_inputfiles.sName AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName This query takes out the duplicates in ciSEID - you will notice there are two lines missing. sName ciSEID ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 37769983 2 40 2.8 TM FB08SPLASH Client 1 37769990 2 40 2.8 TM FB08SPLASH Client 1 37770017 6 192 13.44 TM ELIST Client 1 37770035 2 40 2.8 TM ELIST Client 1 37770036 2 40 2.8 TM ELIST Client 1 37770046 2 40 2.8 TM FB08SPLASH Client 1 37770074 4 80 5.6 TM ELIST 20 472 33.04 I use the following query: SELECT v_inputfiles.sName , SUM (ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Quantity', SUM (ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCT ciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') This includes the duplicates in the Sum totals. sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 8 160 11.2 TM FB08SPLASH Client 1 20 544 38.08 TM ELIST 28 704 49.28 Notice the totals add up to the same as the first example. My end goal is to get this: sName ciSEqty ciSEvalue ciSECharge ciSEInRefSource ciSEInRefData Client 1 6 120 8.4 TM FB08SPLASH Client 1 14 352 24.64 TM ELIST 20 472 33.04 Fewer lines and removing duplicates from the totals. On Mar 9, 11:07 am, Joel wrote: I still think you have to isolate the problem by eliminating some of the SQL. I think my previous code had a problem because I left out spaces between Select, From, Where, and Group. I also suspect that moving theSUMto the end of the SQL will eliminate the duplicates. hre is the code I modified using you latest SQL and putting in the missing spaces (at the end of From, where, group) SelectSQL = "SELECT v_inputfiles.sName ," & _ "ciSE.ciSEInRefSource, " & _ "ciSE.ciSEInRefData " FromSQL = "FROM chronosv2.dbo.ciSE ciSE, " & _ "chronosv2.dbo.v_inputfiles v_inputfiles " WhereSQL = "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ "((ciSE.ciSEbillingmonth='2008-10')) AND " & _ "ciSE.ciSEID IN ( SELECTDISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) " GroupSQL = "GROUP BY v_inputfiles.sName, " & _ "ciSE.ciSEInRefSource, " & _ "ciSE.ciSEInRefData " HavingSQL = "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') " SumSQL = "SUM(ciSE.ciSEqty) AS 'Quantity', " & _ "SUM(ciSE.ciSEvalue) AS 'Value', " & _ "SUM(ciSE.ciSECharge) AS 'Charge' " Sql = SelectSQL & FromSQL & WhereSQL & GroupSQL & HavingSQL & SumSQL "Lexster" wrote: Thank you so much for responding. Unfortunately, your suggestion did not work. I get an error message "Incorrect Syntax near '&'. Statement(s) could not be prepared." I have modified my top statement as: SELECT v_inputfiles.sName AS 'Client Name', ciSE.ciSEqty AS 'Quantity', ciSE.ciSEvalue AS 'Value', ciSE.ciSECharge AS 'Charge', ciSE.ciSEInRefSource AS 'Source', ciSE.ciSEInRefData AS 'Program' FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty , ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') ORDER BY v_inputfiles.sName However I still get more rows listed from the v_inputfiles.Sname than I want. Another dilemma is that when I try tosumQuanty, Value, and Charge, the total will include duplicates even though I am trying to rid duplicates in my Where IN ( SELECTDISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) statement. This is the query I tried to use - it returns summary lines for v_inputfiles.name (which is what I want) but it includes duplicates in the totals for Quantity, value, Charge: SELECT v_inputfiles.sName ,SUM(ciSE.ciSEqty) AS 'Quantity',SUM (ciSE.ciSEvalue) AS 'Value',SUM(ciSE.ciSECharge) AS 'Charge', ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) AND ciSE.ciSEID IN ( SELECT DISTINCTciSE.ciSEID FROM chronosv2.dbo.ciSE ciSE) GROUP BY v_inputfiles.sName, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData<'') I am using MS Query in Excel. Thanks again for responding... you're the only one so far. All the best, Lex On Mar 5, 1:03 pm, Joel wrote: People forget the SQL's are long strings try this SelectSQL = _ "SELECTDISTINCTv_inputfiles.sName AS 'Client Name'," & _ "ciSE.ciSEqty," & _ "ciSE.ciSEvalue," & _ "ciSE.ciSECharge," & _ "ciSE.ciSEInRefSource," & _ "ciSE.ciSEInRefData" FromSQL = _ "FROM chronosv2.dbo.ciSE ciSE," & _ "chronosv2.dbo.v_inputfiles v_inputfiles" WhereSQL = _ "WHERE ciSE.ciSEclient = v_inputfiles.sID AND " & _ "((ciSE.ciSEbillingmonth='2008-10'))" GroupSQL = _ "GROUP BY v_inputfiles.sName, " & _ "ciSE.ciSEID, " & _ "ciSE.ciSEqty, " & _ "ciSE.ciSEvalue, " & _ "ciSE.ciSECharge, " & _ "ciSE.ciSEInRefSource," & _ "ciSE.ciSEInRefData" HavingSQL = _ "HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST')" Sql = SelectSQL & FromSQL & WhereSQL & HavingSQL "Lexster" wrote: Hi everyone. I hope someone can help me out with a SQL formula I am using with MS Query in Excel. This is what I have right now: SELECTDISTINCTv_inputfiles.sName AS 'Client Name', ciSE.ciSEID AS 'Transaction ID', ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData FROM chronosv2.dbo.ciSE ciSE, chronosv2.dbo.v_inputfiles v_inputfiles WHERE ciSE.ciSEclient = v_inputfiles.sID AND ((ciSE.ciSEbillingmonth='2008-10')) GROUP BY v_inputfiles.sName, ciSE.ciSEID, ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge, ciSE.ciSEInRefSource, ciSE.ciSEInRefData HAVING (ciSE.ciSEInRefSource='TM') AND (ciSE.ciSEInRefData='ELIST') What I need help with is adjusting this SQL command so that I get rid ciSE.ciSEID AS 'Transaction ID' but still maintain the same results.. I used the ciSE.ciSEID field because it was used to filter out duplicates. However when I take this field out and use theDistinct, mysumincludes all the duplicates. My goal is to have thesumof ciSE.ciSEqty, ciSE.ciSEvalue, ciSE.ciSECharge grouped by v_inputfiles.sName AS 'Client Name' - which will bring over fewer rows. I would greatly appreciate any suggestions and help on this. Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
distinct | Excel Worksheet Functions | |||
Distinct | Excel Worksheet Functions | |||
Distinct Value With Countif | Excel Worksheet Functions | |||
Distinct Value With Countif | Excel Worksheet Functions | |||
Count distinct | Excel Worksheet Functions |