?
Solved

Data Merging

Posted on 2003-03-03
8
Medium Priority
?
164 Views
Last Modified: 2010-04-07
Hi there

I'm trying to do some calculations on some data in a SQL database. I have a formula in a text field of the form a/b or a*b/c etc

the figures to be subitituted are in another table referenced against the relavent letter.

I need to save the calculated figure in another table.

The way i thaught about going about this is to substitute  the figures into the formula, and then do the calculation.

I'm not quite sure how to put this into action.

Any help would be greatly appreciated.

thanks

Gareth
0
Comment
Question by:gjacksonbeacon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 2

Expert Comment

by:Sweat
ID: 8058341
Gareth,

Good question.

You say "in a SQL database" do you mean in MSSQL or SQL Server?  If so, you need to explore the stored procedure functions of SQL Server. There you'll be able to build formulas and then plug in the variables.  You may need a different procedure for each of the formulas that you require.

If, however, the database is something like Access or MySQL then you don't have the benefit of stored procedures and will need to do a bit more coding.

I would hope that you have a finite number of formulas to be used.  If so, and no more will be added, you have two choices as I see it.
  1) To build a parser that will take a formula, break it into components, evaluate the process to establish the heirarchy of math and then perform the calculations.
  2) Hard code, such as in a Select Case statement each of the formulas and then use your variables in the formulas to get the results.

Maybe this will help, if not please let me know.

Sweat

PS: I assume you are having no problems with the various tables, getting and updating records.


0
 

Author Comment

by:gjacksonbeacon
ID: 8058429
I am referencing a SQL 2000 server database, and i have all the accesses to the database, I was trying to do the calculations within VB.
I was trying to stay away from SQL stored procedures, as i don't know much about them.

gareth
0
 
LVL 2

Expert Comment

by:Sweat
ID: 8059255
Gareth,

Okay.  I'm not very familar with SQL Server stored procedures either only what I've read, it's just that from what you mentioned, it seems like that would be the most appropriate solution.

The alternative, as I mentioned, would be to either write your own formula parser or hardcode the formulas into your VB program.

Select Case rsFormula!Formula
    Case "a/b"
       ' code to do math
    Case "a * b/c"
       ' code to do math
End Select

and so on.  This way will be quick, but won't provide any easy method of expanding new formulas as you would need to add code each time a new formula was added.  And, if you choose this way, you wouldn't need to store the formulas in a table anyway.

The last option would be to build your own parser.

According to Microsoft the following is the precedence of math operations:

Arithmetic/Concatenation Operators
     Exponentiation (^)
     Negation (–)
     Multiplication and division (*, /)
     Integer division (\)
     Modulus arithmetic (Mod)
     Addition and subtraction (+, –), String concatenation (+)
     String concatenation (&)

So if you parse the following formula   a - b / c  where the values are  10 - 10 / 2 and you have no parentheses surrounding portions of the formula and you "walk" through the formula and do the calcs the result would provide a Division by Zero error (10-10=0  0 / 2 ).

If you provide parentheses then the formula should look like  a - (b / c)  and therefore  10 - (10 / 2) which would equal 5.  But to "walk" through this formula would require you to read the whole formula, storing values based on nested parentheses and still evaluating each precedence, then performing calcs and adding results until all aspects are done.

Okay. So your parser would need to build the formula looking for the various operators " * - / + " and then begin to group values together or perform calcs based on the precedence.

Needless to say, I think that you have a bit of work cut out for you to accomplish this.  Not impossible, but you're moving into the rhelm of writing a runtime or part of a compiler.  (My opinion)

You're call, but I can't help but think that Stored procedures would be easier to learn (I haven't tried to learn them yet either so I may be way off base on this) than to write and fully debug a parser.  Unless someone else already has and you can get the code for it.

Just my opinion, which may not be saying much.

Sweat

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:Sweat
ID: 8059319
Gareth,

Okay.  I'm not very familar with SQL Server stored procedures either only what I've read, it's just that from what you mentioned, it seems like that would be the most appropriate solution.

The alternative, as I mentioned, would be to either write your own formula parser or hardcode the formulas into your VB program.

Select Case rsFormula!Formula
    Case "a/b"
       ' code to do math
    Case "a * b/c"
       ' code to do math
End Select

and so on.  This way will be quick, but won't provide any easy method of expanding new formulas as you would need to add code each time a new formula was added.  And, if you choose this way, you wouldn't need to store the formulas in a table anyway.

The last option would be to build your own parser.

According to Microsoft the following is the precedence of math operations:

Arithmetic/Concatenation Operators
     Exponentiation (^)
     Negation (–)
     Multiplication and division (*, /)
     Integer division (\)
     Modulus arithmetic (Mod)
     Addition and subtraction (+, –), String concatenation (+)
     String concatenation (&)

So if you parse the following formula   a - b / c  where the values are  10 - 10 / 2 and you have no parentheses surrounding portions of the formula and you "walk" through the formula and do the calcs the result would provide a Division by Zero error (10-10=0  0 / 2 ).

If you provide parentheses then the formula should look like  a - (b / c)  and therefore  10 - (10 / 2) which would equal 5.  But to "walk" through this formula would require you to read the whole formula, storing values based on nested parentheses and still evaluating each precedence, then performing calcs and adding results until all aspects are done.

Okay. So your parser would need to build the formula looking for the various operators " * - / + " and then begin to group values together or perform calcs based on the precedence.

Needless to say, I think that you have a bit of work cut out for you to accomplish this.  Not impossible, but you're moving into the rhelm of writing a runtime or part of a compiler.  (My opinion)

You're call, but I can't help but think that Stored procedures would be easier to learn (I haven't tried to learn them yet either so I may be way off base on this) than to write and fully debug a parser.  Unless someone else already has and you can get the code for it.

Just my opinion, which may not be saying much.

Sweat

0
 
LVL 2

Expert Comment

by:Sweat
ID: 8059336
Gareth,

I just did a search on google for    "visual basic" math parser    and came up with listings for a bunch of companies selling dll's to do the job. Unfortunately most are commercial and seem to be in the $150 range.  At least one is a shareware but who knows how good it really is.

Sweat

0
 
LVL 1

Accepted Solution

by:
lucho_nd earned 1400 total points
ID: 8059559
You should:

- Save the value of textbox in a string variable (for ex strFormula).
- For every char the formula, verify if it is a letter, in this case search the letter in the table and add the value to the variable strNewFormula. If the char isn´t a letter, add to the variable strNewFormula.
- Use strNewFormula to execute a SQL query.

Pseudo-code:

strNewValue = ""
strNewFormula = ""
strFormula = txtFormula.text
' For every letter in the formula
For lngIndex = 1 To Len(strFormula)
    strChar = Mid(strFormula, lngIndex, 1)
    ' You must implement IsLetter function
    If IsLetter(strChar) then
       ' You must implement SearchInTable function
       strNewValue = SearchInTable(strChar)
    Else
       strNewValue = strChar
    End If
    strNewFormula = strNewFormula & strNewValue
Next lngIndex
' You must complete SQL query
strSQL = "SELECT " & strNewFormula & " AS TOTAL FROM " ...
' Create a recordset and execute SQL Query
...
rst.Open strSQL
MsgBox rst.Fields("TOTAL").Value

This code is not optimal, a letter could be repeated and you must not search again (you could save the found values in a array and search there for example)

Good luck

lucho_nd
0
 
LVL 1

Expert Comment

by:lucho_nd
ID: 8059590
The last mail is for one-letter variables and basic operations (+,-,/,*), for the other you must evaluate every operator and assign it the sign or stored procedute correct

good luck again

lucho_nd
0
 

Author Comment

by:gjacksonbeacon
ID: 8063464
thats wonderfull, good food for thought thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

764 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