Combine results in two different fields

[BCJourney]

I have a table with 3 fields. [shape_type] [letter] [count] I want to extract the first word in the [shape_type], combine all that are the same shape and letter and add together the count.

here is an example:

current state Bevel - virtual, A, 5; Bevel - physical, A, 2;



What I want it to return: Bevel A 7
I don't care about the virtual or physical designation, i want to combine the two.

thank you

[June7]

Ideally, Bevel and virtual/physical would be stored in two fields.

I will presume there are other values besides 'Bevel' unless you say otherwise. Will there always be data? Will there always be both parts? Will there always be a hyphen?

Try:

SELECT Left([shape_type], InStr([shape_type],"-")-1) & [letter] As NewData, Sum([count]) As SumCount FROM tablename GROUP BY Left([shape_type], InStr([shape_type],"-")-1) & [letter];

[Bob Fitz]

You could try:
SELECT [A] & [total] AS Result, Left([shape_type],InStr([shape_type],"-")-2) & ", " & [letter] & ", " AS A, Sum(tblYourTable1.count) AS Total
FROM tblYourTable1
GROUP BY Left([shape_type],InStr([shape_type],"-")-2) & ", " & [letter] & ", ";

You will need to replace tblYourTable1 with your table name.

[BCJourney]

I tried both sets of code and I get the "Invalid procedure call" error each time. Maybe the actual data will make this clearer, although you both seem to know what is going on.
Record 1:
[field 1] Intel Based - Logical
[field2] High
[field 3] 856

Record 2:
[field 1] Intel Based - Logical
[field2] Low
[field 3] 13

Record 3:
[field 1] Intel Based - Physical
[field2] High
[field 3] 25

Record 4:
[field 1] Intel Based - Physical
[field2] Low
[field 3] 516

I need to combine records 1 and 3 & records 2 and 4. The results would be:
Intel Based High 881
Intel Based Low 529

Fields 1 and 2 can be combined into one, but i need field 3 in a separate field. In other words, I need to add together those classified as physical and logical into one record for each category. I would redesign the table but This is a large complicated database and this was just now requested so I don't want to re-invent the wheel.

[June7]

Syntax of suggested queries are both correct. Where have you tried to use? Post the query statement and/or code for analysis.

[BCJourney]

SELECT DISTINCTROW data_tbl.Service_Type, data_tbl.Service_Item, Format$([data_tbl].[Billing_Month],'mmmm yyyy') AS [Billing_Month By Month], Sum(data_tbl.Service_Count) AS SumOfService_Count
FROM data_tbl INNER JOIN Scorecard_cost_per_mgd_server_max_month ON data_tbl.Billing_Month = Scorecard_cost_per_mgd_server_max_month.MaxOfBilling_Month
GROUP BY data_tbl.Service_Type, data_tbl.Service_Item, Format$([data_tbl].[Billing_Month],'mmmm yyyy'), Year([data_tbl].[Billing_Month])*12+DatePart('m',[data_tbl].[Billing_Month])-1
HAVING (((data_tbl.Service_Item)="Critical" Or (data_tbl.Service_Item)="High" Or (data_tbl.Service_Item)="Medium" Or (data_tbl.Service_Item)="Low"));

[June7]

I don't see anything in that query dealing with parsing shape_type field.