GROUP ALL LIKE RECORDS AND SUM?

[Turb]

All,
I've been trying to accomplish this and can't seem to find a direction to go even after searching the FAQ's and this forum. Can someone please get me started or at least point me in the right direction?

I have a table structured like the one below:
FIELDA FIELDB FIELDC FIELDD FIELDE
STB D01 A10101 XXTE 500
STB D01 A10101 XXTE 25
STB D01 A10101 ZZOP 10
STB D06 AC0201 XXTC 350

I need to create a query that will sort all the records in the table by like values in the FIELDC column then group them by the FIELDD column and then total all of those records by the values in FIELDE.
Needed result from the above table example:
FIELDA FIELDB FIELDC FIELDD FIELDE
STB D01 A10101 XXTE 525
STB D01 A10101 ZZOP 10
STB D06 AC0201 XXTC 350

I would then make a new table from the results.
Please help!

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[Remou]

Do you mean something like (SQL view of query)?
[tt]SELECT tblTable.FieldA, tblTable.FieldB, tblTable.FieldC, tblTable.FieldD, Sum(tblTable.FieldE) AS SumOfFieldE
FROM tblTable
GROUP BY tblTable.FieldA, tblTable.FieldB, tblTable.FieldC, tblTable.FieldD;[/tt]

 

[hneal98]

SELECT 
    Table1.FieldA, 
    Table1.FieldB, 
    Table1.FieldC, 
    Table1.FieldD, 
    Sum(Table1.FieldE) AS SumOfFieldE
FROM 
    Table1
GROUP BY
    Table1.FieldA,
    Table1.FieldB,
    Table1.FieldC,
    Table1.FieldD;

 

[Turb]

Remou & hneal98,
Thank you for your responses.

Remou, hneal98 is close to what I need to do.

hneal98, as I stated above this is close, but does not sum FIELDE, it just makes a copy of the table with an extra field called 'SumOfFieldE' that contains the same information as FIELDE does.

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[hneal98]

I beg to differ with you. It does add the 500 to the 525. You are not supposed to list FieldE. Only the sum of FieldE. If you list FieldE, then, yes; you will get what you discribed.

 

[Turb]

hneal98,
I beg your pardon. You are correct.
Because my table has a "timestamp" field as a unique ID, NO record will be the same as another and it seemed that the query was not totaling.
I had neglected to take this into account.

Thank you for your help!

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[Turb]

hneal98,
How is it that the 'Report Wizard' can generate the type of grouping and summation that I need based on the same table? I don't understand.

If I run the report wizard, select all my fields for placement on the report, group by FIELDC and then FIELDD, set my summary options to sum FIELDE I get exactly the type of group/sort and summartion I need in the query regardless of the timestamp.

Is it at all possible to do this in a SQL or make-table query?

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[hneal98]

I am not sure I am following your question. The report wizard is supposed to be able to generate the type of grouping and summation you need. So I am not clear on what you are asking.

 

[Turb]

hneal98,
The report wizard does generate the grouping and summation I need; but only if I'm creating a report.

I need to be able to do the same thing in a query so I can make a new table based on the data from the query.

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[hneal98]

That is why I stated that you shold not show Field E, only the summation of it. If you show FieldE in your query, all rows will be displayed and none will sum.