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.

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.

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.

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.

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.

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.

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.