Sum Query

[mfuada]

Hi guys... i want to do sum query by selection, i have these datas on singel table:
A1 = 50
A2 = 10
A3 = 30
B1 = 20
B2 = 30
C1 = 20
C2 = 20
and i would like to create a query that sum just "A","B",&"C" so the on the datasheet would be :
Sum of A = 90 ; Sum of B = 50 ; Sum C = 40
is there anyway i can do that? is there any suggestion?
Thx...

 

[wiklendt]

have you tried doing a crosstab query (use the query wizard - it will prompt you for all the bits it needs).

 

[ByteMyzer]

Assuming you have a table like the following:

MyTable
-------------------
MyField1 | MyField2
-------------------
A1       |       50
A2       |       10
A3       |       30
B1       |       20
B2       |       30
C1       |       20
C2       |       20

Try a query like the following (substitute highlighted text with actual table/field names):

SELECT Left(T1.[b][i]MyField1[/i][/b], 1) AS [b][i]MyField1[/i][/b], Sum(T1.[b][i]MyField2[/i][/b]) AS [b][i]MyField2[/i][/b]
FROM [b][i]MyTable[/i][/b] T1
GROUP BY Left(T1.[b][i]MyField1[/i][/b], 1);

 

[mfuada]

suppose i have a table like this:
Table1
-------------------
Product | Price
-------------------
A1 | 50
A2 | 10
A3 | 30
B1 | 20
B2 | 30
C1 | 20
C2 | 20

i'm thinking more of a grouping the field with value "A" and then sum the total of the field of A and so on... so my rough idea of the SQL would be

SELECT Sum(table1.price) AS SumOfprice WHERE product like "A*"
FROM table1;

so the result will be

SumofA = 90

 

[khawar]

You can use the sql below to get all totals in a single query

SELECT 
Left([Product],1) AS productFirst, 
Sum(Table1.Price) AS PriceTotal
FROM Table1
GROUP BY Left([Product],1);