best way to automate deleting records and adding filters in a quer

[Guest]

If I need all of the fields in my main Asset table, how do I sum like records
by assetnumber? There is a GLacct number that is different so it won't sum
the records.

assetnumber=123,GLacct=161750,cost=0,deprec=0,flag=" "
assetnumber=123,GLacct=161700,cost=5000,deprec=500,flag=R
assetnumber=123,GLacct=161750,cost=1000,deprec=200,flag=" "

The summed record that I would like is:
assetnumber=123,GLacct=161750,cost=6000,flag=" "

Is this possible to do?

Thank you.
gigi

 

[arthurjr07]

SELECT assetnumber,GLacct,SUM(cost) FROM AssetTable Group By
assetnumber

 

[John Spencer]

You could use an aggregate query to do this.

Assumptions:
You want
-- the largest GLAcct
-- any Flag
-- Sum of Cost and Depreciation

In the SQL window, that would look something like:

SELECT AssetNumber
, Max(GLAcct) as GL_Acct
, Sum (Cost) as TotalCost
, Sum(Deprec) as TotalDeprec
, First(Flag) as aFlag
FROM AssetTable
GROUP BY AssetNumber

If you are doing this in the query grid, select View: Totals from the menu
and in the new Totals line select the appropriate operation.

 

[Guest]

thank you.

You could use an aggregate query to do this.

Assumptions:
You want
-- the largest GLAcct
-- any Flag
-- Sum of Cost and Depreciation

In the SQL window, that would look something like:

SELECT AssetNumber
, Max(GLAcct) as GL_Acct
, Sum (Cost) as TotalCost
, Sum(Deprec) as TotalDeprec
, First(Flag) as aFlag
FROM AssetTable
GROUP BY AssetNumber

If you are doing this in the query grid, select View: Totals from the menu
and in the new Totals line select the appropriate operation.