group by first three characters of a value

  • Thread starter Thread starter gls858
  • Start date Start date
G

gls858

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;
 
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.
Click to expand...

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;
 
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
 
John said:
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;
Click to expand...

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

gls858
 
Back
Top