selective quiry

[gisash1]

I have a table with three fields. The first field with furniture styles. The second field with quantity and the third one with dollars value. It looks like that:

Style Qty Dollars
1020-10 1 20
1020-20 1 30
1020-40 2 50
2030-10 1 60
2030-42 5 40
LV6050-10 1 50
LV6050-30 2 90

I need summarize all styles on the bases of the data before "-"
The table should be like that:

1020 4 100
2030 6 100
LV6050 3 140

Could anybody show me the way to build the criteria for such a quiry?

Thank you very much in advance

 

[KenHigg]

Do a new query based on your table. Make it a totals query. In col. 1, enter something like MySubCode: left([style],4). Then let the query group on the this col. Then do a scond col. where you do something like: MyStyleTotal: [qty] * [dollars]. And let the query sum this col.


???
ken

 

[Jon K]

Type/paste in the SQL View of a new query, replacing with the correct TableName:-

SELECT Left([TableName].[Style],InStr([TableName].[Style],"-")-1) AS Style,
Sum([TableName].[Qty]) AS SumOfQty, Sum([TableName].[Dollars]) AS SumOfDollars
FROM [TableName]
GROUP BY Left([TableName].[Style],InStr([TableName].[Style],"-")-1);
.

 

[gisash1]

just tried to do that but... Invalid procedure call

 

[gisash1]

it works when I type this

SELECT Left([TableName].[Style],4) AS Style

But unfortunately I have styles like LV7856

Shooot, please help!!

 

[Jon K]

Try the database attached, which contains your sample data.
.

 

[gisash1]

I think I got it why it's not working in my case. As it's turned out, there are some styles without "-" like 92456.

 

[Jon K]

You can use the IIF() function to test for the existence of "-"

SELECT IIf(Instr([TableName].[Style],"-"),Left([TableName].[Style],InStr([TableName].[Style],"-")-1),[TableName].[Style]) AS Style,
Sum([TableName].[Qty]) AS SumOfQty, Sum([TableName].[Dollars]) AS SumOfDollars
FROM TableName
GROUP BY IIf(Instr([TableName].[Style],"-"),Left([TableName].[Style],InStr([TableName].[Style],"-")-1),[TableName].[Style]);
.

 

[gisash1]

Oh Yeahhh!

Thanks Man. You are good! Really goood!

That's exactly what I need.
Thanks a lot!!!