Can't get my Grouping right

[chrisb1981]

I want to group my report by field "Nominal" but I need the grouping on part of the field. The Nominal field is a number field and is 6 digits long. E.g entries in the Nominal field are like this: 100110, 100120, 100130, 100140, 105110, 105120, 105130, etc. I need to group and subtotal on the first 3 digits in this case "100" and "105". Basically the first 3 digits relate to type of product and the last three digits relate to area I need the report to show 1 subtotal for the codes starting 100 and another for codes starting 105 etc.

I have a report already setup but it groups by all 6 digits and I end up with 6 subtotals per product. So calculating how much was earned per product requires adding up all the subtotals for each product. If there is an easier way can someone please let me know.

 

[KenHigg]

You can parse out the 3 left most charactors by converting it to a string and the use the left() function. I would do all of this in a new query creating this new field and then use a second query to do the grouping...

 

[chrisb1981]

Thanks for the quick reply Ken. I am fairly new to access and I don't know what you mean. Could you explain what to do or point me to where I might be able to read about it. My current coding is:

SELECT SalesForecast.CompanyName, SalesForecast.CourseName, SalesForecast.TotalGross, SalesForecast.TotalDiscount, SalesForecast.TotalNet, SalesForecast.SalesOrder, SalesForecast.InvoiceNumber, SalesForecast.InvoiceDate, SalesForecast.MovedDate, SalesForecast.MovedDate2, SalesForecast.MovedDate3, SalesForecast.Nominal, SalesForecast.CustomerAccountNumber, SalesForecast.CreditNumber
FROM SalesForecast
WHERE (((SalesForecast.Month)>=[forms]![Nominal Code Range]![Completion Start Date] And (SalesForecast.Month)<=[forms]![Nominal Code Range]![Completion End Date]) AND ((SalesForecast.Nominal)>=[forms]![Nominal Code Range]![Start No] And (SalesForecast.Nominal)<=[forms]![Nominal Code Range]![End No]))
GROUP BY SalesForecast.CompanyName, SalesForecast.CourseName, SalesForecast.TotalGross, SalesForecast.TotalDiscount, SalesForecast.TotalNet, SalesForecast.SalesOrder, SalesForecast.InvoiceNumber, SalesForecast.InvoiceDate, SalesForecast.MovedDate, SalesForecast.MovedDate2, SalesForecast.MovedDate3, SalesForecast.Nominal, SalesForecast.CustomerAccountNumber, SalesForecast.CreditNumber;

can this be changed?

 

[KenHigg]

Base the query you now have on another query instead of a table and in that query use the left() funtion on the field you wish to group on on...