Solved

Add filds in access usin SQL code

Posted on 2009-04-09
5
176 Views
Last Modified: 2012-05-06
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                
0
Comment
Question by:Qw M
  • 3
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24107390
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;
0
 

Author Comment

by:Qw M
ID: 24107869
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!  
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24108063
why didn't you specify in the first place that you have decimal values.


SELECT pontage.name, pontage.h1, pontage.h2, pontage.h3, pontage.h4, pontage.h5, FormatNumber(IIf(IsNumeric([h1]),cdbl([h1]),0)+IIf(IsNumeric([h2]),cdbl([h2]),0)+IIf(IsNumeric([h3]),cdbl([h3]),0)+IIf(IsNumeric([h4]),cdbl([h4]),0)+IIf(IsNumeric([h5]),cdbl([h5]),0),2) AS WH
FROM pontage;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24110655
did the sql work?
0
 

Author Comment

by:Qw M
ID: 24113641
Thank you!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question