Merging columns in table

[galantis]

Hi,

I have a problem, and I am not sure what's the best way.

My query using Sum, and GroupBy that produce a result of

Name, Quantity, Price, Type
AC32, 755.95,13698, LM
AC32, 320.89, 1736,EA

but I would like it such that,
Name, Quantity(LM), SumOf LM+EA,
AC32, 755.95, 15434,

Is that enough information?
Thanks in advance.

 

[Liv Manto]

Name, Quantity, Price, Type
AC32, 755.95,13698, LM
AC32, 320.89, 1736,EA

but I would like it such that,
Name, Quantity(LM), SumOf LM+EA,
AC32, 755.95, 15434,


Create a crosstab query, drag all fields

name = group

quantity where = lm

price = sum

 

[galantis]

Sorry, I don't understand. Cross tab is new to me.

which heading for row heading and which one for column heading? and which one is for value?

 

[Liv Manto]

Call your table "Table1"

then create a query, do not drag a table, edit the sql,
paste this.


SELECT Table1.Name, Table1.Type, Table1.Quantity, SUMALL.SumOfPrice

FROM Table1 LEFT JOIN
(SELECT Table1.Name, Sum(Table1.Price) AS SumOfPrice
FROM Table1
GROUP BY Table1.Name) as SumALL


ON Table1.Name = SUMALL.Name
GROUP BY Table1.Name, Table1.Type, Table1.Quantity, SUMALL.SumOfPrice

HAVING (((Table1.Type)="lm"));

 

[galantis]

Thanks Liv,

it worked!

(my SQL is not that strong!)