Combine rows by criteria and sum of values - tricky! 1

[LEICJDN1]

Hi all,

Have an interesting problem.

We have a vey large dataset which we need to pare down. Bascially one field denotes a certain energy and another the resultant force like this:

......
500.02 225
500.04 250
500.06 300
500.10 450
501.35 550
501.45 012
501.65 450
......

In order to make it more manageable, we wish to simplify the energy fiels down into integers, and to then sum all the forces for that integer range into one total thus:

500 1225 (eg.250+300+450+550)
501 1012

I am sure this can be done but my brain is failing me today. Anyone point me in the right direction to a solution?

Thanks.

 

[PHV]

Something like this ?
SELECT Int([name of energy field]) As Energy, Sum([name of forces field]) As Forces
FROM [name of table]
GROUP BY Int([name of energy field])
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LEICJDN1]

Thanks.

I now have this in an Access query:

(SELECT Int( [test]![Field1] ) As Energy, Sum( [test]![Field2] ) As Forces
FROM [test]
GROUP BY Int( [test]![Field1] )
; )

I get an error telling me the query can return more than one field without using the exists reserved word in the main query's FROM clause.

It asks me to revise the SELECT statement to only request one field.

Any ideas?

 

[PHV]

Can you please post the WHOLE sql code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LEICJDN1]

Your reply makes me wonder if I am either being very stupid or am missing something as that is all the code I have! I was hoping I could just put this code into the query expression builder section and run it?

Please enlighten me as to my stupidity!

 

[PHV]

Go in the Query Design Window (Create a new query) and choose the SQL pane, then paste the code I posted and choose to view data.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LEICJDN1]

PHV. Thanks for your help but I am struggling here. I have created Queries in the past no problem but as I said I think my brains is going.

I have a dummy table [test] with just three fields - [Field1] and [Field2] and an ID, and populated it with 10 lines of test data.

I open Query design, it then asks me which table to choose, so I choose test.

Go to SQL, paste your code, change the names to the above and I have this:

SELECT Int([test]![Field1]) As Energy, Sum([test]![Field2]) As Forces 
FROM [test]
GROUP BY Int([test]![Field1])
;

This throws a compile error in query expression Int([test]![Field1])

I am sure I am either missing the obvious or just being plain dumb here..

 

[PHV]

And this ?
SELECT Int([Field1]) As Energy, Sum([Field2]) As Forces
FROM [test]
GROUP BY Int([Field1])
;
Or this ?
SELECT ([Field1]\1) As Energy, Sum([Field2]) As Forces
FROM [test]
GROUP BY ([Field1]\1)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LEICJDN1]

PH,

Second works well but now have an issue with rounding. If the values for one Energy group are, say, 1001.12 or 100.23 then no problem, but values such as 1001.56 are clearly being rounded up and included in the 1002 group.

Are you able to help further?

 

[PHV]

Perhaps this ?
SELECT (([Field1]-0.5)\1) As Energy, Sum([Field2]) As Forces
FROM [test]
GROUP BY (([Field1]-0.5)\1)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LEICJDN1]

PH, you are extremely knowledgable and helpful. Thanks for your swift reply, and your patience!