Using Multiple Column sum in Query

[ootkhopdi]

Hi all

i create an query in Access 2007 which is select query

having many fields Like Pay1, Pay2, Pay3 Pay4 .....Pay 10

I want to create a field in this query which show sum of many fields like Pay 3+Pay4+Pay5+Pay6

How can i create and sum these

pls give me help

thanks in advance

 

[arnelgp]

select (nz(pay1,0)+nz(pay2,0)+nz(pay3,0)...) As TotalPay from yourtablename.

 

[benedictine191]

or you could use
select ([pay 1]+[pay 2]+[pay 3]+[pay 4]) as 'pay total',* from tablename

 

[plog]

SQL has a SUM function:

SELECT SUM([YourField]) FROM YourTable;

That would make this trivial. Unfortunately, you have an improperly structured database. You shouldn't have numerated field names (Pay1, Pay2, Pay3...). Data should be stored vertically (with more rows) and not horizontally (with more columns).

You need to properly structure your database. Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and get your table set up properly or you will continue to have issues like this that should be relatively simple to achieve.