Link to home
Start Free TrialLog in
Avatar of Qw M
Qw MFlag for United States of America

asked on

Add filds in access usin SQL code

I have a table named "Pontage", in this table I have 11 filds: name, H1, H2, H3, H4, H5, WH. In this fild i can write text. I will write data in the first 6 filds. I need a updata query to calculate WH  (please see the example). WH add H1 to H5,

Please help me!
Thank you

example
Name     |   H1   |   H2   |   H3    |   H4   |   H5   ||  WH    
Andy          5         co       L          8         2    ||   15      
Mark           8         8         CM     CM       CM  ||    16                
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

it is not a common practice to store calculated data to a table.
you can always derive the accurate result using a query, in case any value if any the field change


try this query

SELECT pontage.name, pontage.h1, pontage.h2, pontage.h3, pontage.h4, pontage.h5, IIf(IsNumeric([h1]),CInt([h1]),0)+IIf(IsNumeric([h2]),CInt([h2]),0) +IIf(IsNumeric([h3]),CInt([h3]),0) +IIf(IsNumeric([h4]),CInt([h4]),0) +IIf(IsNumeric([h5]),CInt([h5]),0) AS WH
FROM pontage;
Avatar of Qw M

ASKER

I have one problem with this code. The query put in the "wh" a round value. For eample if i add H1 to H5 and the rezult is 5.8 then in "wh" i will have 6. How can I make the "wh" to show me the exact number with 2 decimals.

Thank you!  
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
did the sql work?
Avatar of Qw M

ASKER

Thank you!