Member Avatar for Josef01

can someone help me to write a query that will sum the information across the row and down the column (sum) in ms access table
a b sum
1 10 11
2 20 22
total_all
I tried the following
select a,b, sum as (a+b) from table1
the error i get :
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

  • 3 Contributors
  • 3 Replies
  • 114 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Josef01

Dani AI

Generated

A few clarifications and practical tips that build on what , and discussed.

The key distinction is between a row-level calculation (a per-row total) and an aggregate (a column total). In Access, a simple calculated column in a select/query will give the per-row value, while SUM and other aggregates collapse rows. Also avoid calling a column alias sum (it shadows the function); pick something like RowTotal or TotalAandB.

Nulls are important in Access: adding a NULL to a number yields NULL. Use NZ to treat NULL as zero when you need arithmetic that ignores missing values. Example of a row-calculated field that is safe for NULLs:

SELECT NZ([a],0) + NZ([b],0) AS RowTotal
FROM table1;

To return detail rows plus a totals row in one result set, append an aggregate query with UNION ALL (each part must have the same column count and compatible types). Example pattern:

SELECT [a], [b], NZ([a],0)+NZ([b],0) AS RowTotal
FROM table1
UNION ALL
SELECT SUM(NZ([a],0)), SUM(NZ([b],0)), SUM(NZ([a],0)+NZ([b],0))
FROM table1;

If you prefer, compute column totals as SUM(NZ([a],0)) + SUM(NZ([b],0)) to avoid losing rows when one column is NULL — that can differ from summing the per-row expression if NULLs exist.

Other quick tips: in the Access query designer use the Totals (sigma) button for vertical sums, put a calculated control in a report footer for a display-only totals row, and avoid wrapping indexed columns in functions inside aggregates if you need performance.

Recommended Answers

All 3 Replies

Member Avatar for Kruptein

the correct statement is:

SELECT a, b, sum(a + b) from table1

sum is an existing method that returns the sum of the passed items

Member Avatar for tomato.pgn

the statement used to produce required output is:-

SELECT a,b,a+b as sum from table1

to get the result across the column i.e. sum of all columns:-

select sum(a),sum(b),sum(a+b) from table1

hope it might be helpful.....

Member Avatar for Josef01

it works fine.thank you,i really appriciate your help

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.