group by first three characters of a value

I have the following query where the Group By value
ECNHDetail.Department is a four digit value. Is is possible to change
this query to group by is based only on the first three characters of
the dept.

Example. I have depts 101a,101b,101c but I want all of the 101's group
together.

SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
ECNHDetail.Department, Sum(ECNHDetail.LineAvgCost) AS SumOfLineAvgCost,
Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
ECNHDetail.Department
HAVING (((ECNHDetail.GLLocation)=" 1") AND ((ECNHDetail.CustomerNumber)
Like "*3584"))
ORDER BY Sum(ECNHDetail.LineAmount) DESC;

gls858

Does your GLLocation field really have leading spaces?
Try this --
SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3) AS DEPT, Sum(ECNHDetail.LineAvgCost) AS
SumOfLineAvgCost, Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3)
HAVING (((ECNHDetail.GLLocation)=" 1") AND ((ECNHDetail.CustomerNumber) Like
"*3584"))
ORDER BY Sum(ECNHDetail.LineAmount) DESC;

--
Build a little, test a little.


"gls858" wrote:

I have the following query where the Group By value
ECNHDetail.Department is a four digit value. Is is possible to change
this query to group by is based only on the first three characters of
the dept.

Example. I have depts 101a,101b,101c but I want all of the 101's group
together.

SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
ECNHDetail.Department, Sum(ECNHDetail.LineAvgCost) AS SumOfLineAvgCost,
Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
ECNHDetail.Department
HAVING (((ECNHDetail.GLLocation)=" 1") AND ((ECNHDetail.CustomerNumber)
Like "*3584"))
ORDER BY Sum(ECNHDetail.LineAmount) DESC;

gls858

On Fri, 10 Jul 2009 17:13:35 -0500, gls858 wrote:

I have the following query where the Group By value
ECNHDetail.Department is a four digit value. Is is possible to change
this query to group by is based only on the first three characters of
the dept.

Example. I have depts 101a,101b,101c but I want all of the 101's group
together.

Sure. Just use the Left() function:

SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3), Sum(ECNHDetail.LineAvgCost) AS
SumOfLineAvgCost,
Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3)
HAVING (((ECNHDetail.GLLocation)=" 1") AND ((ECNHDetail.CustomerNumber)
Like "*3584"))
ORDER BY Sum(ECNHDetail.LineAmount) DESC;

Since GLLocation and CustomerNumber are in the table rather than being
calculated as an aggregate, I'd move their query criteria from the HAVING
clause (which is applied AFTER all the calculations) into the WHERE (to limit
the number of records BEFORE doing the calculations:

SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3), Sum(ECNHDetail.LineAvgCost) AS
SumOfLineAvgCost,
Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
AND (((ECNHDetail.GLLocation)=" 1")
AND ((ECNHDetail.CustomerNumber) Like "*3584"))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3)
ORDER BY Sum(ECNHDetail.LineAmount) DESC;
--

John W. Vinson [MVP]

Sorry for the delay. I posted this last Friday right before I left for
the week end.

Yes it does. It's an older flat file db that I'm importing and the field
is designed to hold 2 digits. Just one of many PITA's I have to deal
with in this data.

gls858



KARL DEWEY wrote:
Does your GLLocation field really have leading spaces?
Try this --
SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3) AS DEPT, Sum(ECNHDetail.LineAvgCost) AS
SumOfLineAvgCost, Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3)
HAVING (((ECNHDetail.GLLocation)=" 1") AND ((ECNHDetail.CustomerNumber) Like
"*3584"))
ORDER BY Sum(ECNHDetail.LineAmount) DESC;

John W. Vinson wrote:
On Fri, 10 Jul 2009 17:13:35 -0500, gls858 wrote:

I have the following query where the Group By value
ECNHDetail.Department is a four digit value. Is is possible to change
this query to group by is based only on the first three characters of
the dept.

Example. I have depts 101a,101b,101c but I want all of the 101's group
together.

Sure. Just use the Left() function:

SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3), Sum(ECNHDetail.LineAvgCost) AS
SumOfLineAvgCost,
Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3)
HAVING (((ECNHDetail.GLLocation)=" 1") AND ((ECNHDetail.CustomerNumber)
Like "*3584"))
ORDER BY Sum(ECNHDetail.LineAmount) DESC;

Since GLLocation and CustomerNumber are in the table rather than being
calculated as an aggregate, I'd move their query criteria from the HAVING
clause (which is applied AFTER all the calculations) into the WHERE (to limit
the number of records BEFORE doing the calculations:

SELECT ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3), Sum(ECNHDetail.LineAvgCost) AS
SumOfLineAvgCost,
Sum(ECNHDetail.LineAmount) AS SumOfLineAmount
FROM ECNHDetail
WHERE (((ECNHDetail.InvoiceDate) Between #1/1/2009# And #6/30/2009#))
AND (((ECNHDetail.GLLocation)=" 1")
AND ((ECNHDetail.CustomerNumber) Like "*3584"))
GROUP BY ECNHDetail.GLLocation, ECNHDetail.CustomerNumber,
Left(ECNHDetail.Department, 3)
ORDER BY Sum(ECNHDetail.LineAmount) DESC;

Thanks John. That worked. Simple when you know how. Appreciate the help.

gls858