How can I create a query that does this

[Guest]

I have 10 fields, for example lets just say they are called field 1, 2, 3 etc.

In each field is a number from 1 to 5, and there are many records.

I need a query that for each field, gets the sum of its contents. Then I
need to display these results in ascending order, like this;
NAME Total

field_5 20
field_10 23
field_1 31
etc.

Is this possible? And how can I do it? I've tried to use all of my (limited)
query knowledge, and searched for answers, but I can't find out a way to do
this

Any help greatly appreciated

 

[Duane Hookom]

I think you can do this with a union query (quni10FieldsNormalized):
SELECT "Field_1" as TheField, Sum(Field_1) as TheTotal
FROM tbl10Fields
UNION
SELECT "Field_2", Sum(Field_2)
FROM tbl10Fields
UNION
--etc---
You can then create a totals query
SELECT TheField, TheTotal
FROM quni10FieldsNormalized
ORDER BY TheTotal;

 

[Sylvain Lafontaine]

The function SUM associated will give you the sums for each field. To
separate them, you can use 10 select queries, one for each field, and 9
UNIONs to associate them and sort them into a single resultset; something
like:

select * from (
select "field_1" as field_name, sum(field_1) as Summation from MyTable
union
select "field_2" as field_name, sum(field_2) as Summation from MyTable
union ...

) as Q order by Summation ASC

This is for SQL-Server but the syntaxe for Access should be about the same.
Of course, you cannot use the query designer to do this because of the UNION
and the SubQuery.

S. L.

 

[Guest]

Perfect! Thankyou very much

The function SUM associated will give you the sums for each field. To
separate them, you can use 10 select queries, one for each field, and 9
UNIONs to associate them and sort them into a single resultset; something
like:

select * from (
select "field_1" as field_name, sum(field_1) as Summation from MyTable
union
select "field_2" as field_name, sum(field_2) as Summation from MyTable
union ...

) as Q order by Summation ASC

This is for SQL-Server but the syntaxe for Access should be about the same.
Of course, you cannot use the query designer to do this because of the UNION
and the SubQuery.

S. L.