Query to group values by first three letters

[jstephens29]

Hi there, I'm a beginner when it comes to access so my knowledge is limited. I'm currently trying to create a query that shows me all of my billing codes used in the month of December that have a sum greater than $5000. The wrinkle is that I have 100's of billing codes that are structured as 3 letters and then an associated number (AAA1, AAA2, etc). I want my results to group the total of all AAA's and just see AAA. I don't want to see AAA1 or AA8 and it's total. Here's a couple of screenshots: one of the beginning part of the query and one of an example of the data I'm working from. Thanks.

[cyanidem]

Left(cht_description,3)

[jstephens29]

Thanks for the reply. I've actually tried that statement under the cht_itemcode field but it only returns the 4 lines that have 3 letter codes and excludes the remainder, which is the majority. From my spreadsheet above I have BLD1 and BLD5. I don't want my results to show them separate. I want the total of BLD1 and BLD5 to be combined and displayed as just BLD. Hopefully that makes sense. Thanks.

[cyanidem]

Putting it "under the cht_itemcode field" you set criteria for that field to return only records with 3-character description. You need to put it as a field in your query meaning in place of cht_itemcode in "Field" row of query designer:

ItemCode:Left([cht_itemcode],3)

And leave the rest of your query untouched.

Edit: I just realised that I used wrong field name in post #2, now it's ok.

[jstephens29]

Ok, this make sense. I just tried what you suggested and unfortunately I'm receiving an error when I run that says, "The specified field [cht_itemcode] could refer to more than one table listed in the FROM clause of your SQL statement".

Here's what the query looks like in SQL view. Do I have it joined incorrectly?

SELECT Left([cht_itemcode],3) AS ItemCode, dbo_chargetype.cht_description, dbo_chargetype.cht_retired, Sum(dbo_invoicedetail.ivd_charge) AS SumOfivd_charge
FROM (dbo_chargetype INNER JOIN dbo_invoicedetail ON dbo_chargetype.cht_itemcode = dbo_invoicedetail.cht_itemcode) INNER JOIN dbo_invoiceheader ON dbo_invoicedetail.ivh_hdrnumber = dbo_invoiceheader.ivh_hdrnumber
WHERE (((dbo_invoiceheader.ivh_billdate) Between #12/1/2015# And #12/31/2015#))
GROUP BY Left([cht_itemcode],3), dbo_chargetype.cht_description, dbo_chargetype.cht_retired
HAVING (((dbo_chargetype.cht_retired) Like "N" Or (dbo_chargetype.cht_retired) Is Null) AND ((Sum(dbo_invoicedetail.ivd_charge))>5000));

[cyanidem]

Apparently you have same named field in more than one table.
Just change:
Left([cht_itemcode],3)
to:
Left([dbo_chargetype.cht_itemcode],3)

[jstephens29]

You beat me to it, just found that mistake in the SQL view and made the change. The query runs just fine now but it's showing me the results with the first three letters and I have multiple FUE's,for example, instead of it grouping all the FUE's into one row with one SUM.

[June7]

Now click the Totals button on ribbon and set the aggregate query properties in the Total row.

[cyanidem]

Remove cht_description field from your query design? It doesn't make sense to keep it there anymore anyway.

[jstephens29]

Thanks June. The Totals button is already selected. What do you recommend I set the aggregate properties in the Total row to?

[jstephens29]

Looks like it worked by removing the cht_description field. Guessing there's no way to make this work and include the item code description...

[June7]

Nope. The description is different for each ItemCode and that becomes a factor in grouping on unique values.

You could build a report using Sorting & Grouping features with aggregate calcs in group and report footers. This allows display of detail info and summary grouping/calcs.

[jstephens29]

Yeah, probably a little past my access abilities right now. This should do for now. Thank you both for your help...

[cyanidem]

Looks like it worked by removing the cht_description field. Guessing there's no way to make this work and include the item code description...

There is, as June said, using report sorting & grouping. But in your query it doesn't make sense to have description if you group records by first 3 letters of code. What description would you like to see for code CLN? There is 7 records with code CLNxxx, so either you can:
1. group it again by description (and see all of them in which case grouping by 3 letters of code is pointless) or
2. remove description or see one of those 7 as description
3. use option 1. + June's suggestion to group it on report.

[June7]

A report group would be defined by the 3 letters. The group header/footer could have aggregate calcs that summarize the details (Sum, Avg, Count, etc).

Below each group header would display the detail records that contribute to that group. This is where the description would show.

Fairly simple to learn basic S&G report design. Well worth your while to take an hour and do so. http://functionx.com/access/Lesson25.htm