Jump to content

How to insert calculation inside access


Guest artrynn

Recommended Posts

Guest artrynn

I'm new in using ASP.I already create the database using microfost acces.I used 3 fields : Price, Quantity, Total.the in the web design (using dreamweaver) i create 3 textfield : price, quantity and Total.When i type the price such as $1(per unit) and quantity 3 so the total will be $3.so.how i can create the calculation and automatically its filled inside the database.i really do not know how to to do the coding.please help me... :)

Link to comment
Share on other sites

I'm new in using ASP.I already create the database using microfost acces.I used 3 fields : Price, Quantity, Total.the in the web design (using dreamweaver) i create 3 textfield : price, quantity and Total.When i type the price such as $1(per unit) and quantity 3 so the total will be $3.so.how i can create the calculation and automatically its filled inside the database.i really do not know how to to do the coding.please help me... :)

I don't recommend creating a seperate "Total" field, it creates too much overhead. Your database structure should look like this:
your_table---------------ID (autonumber)Item (text)Price (currency)Quantity (int)

Here is some sample data:

ID      Item    Price   Quantity1       Cat     $12.00  112       Bunny   $6.99   133       Mouse   $3.99   454       Dog     $16.95  75       Horse   $34.99  52

You can write an SQL statement to calculate the totals automatically, like this:

SELECT   ID,         Item,         Price,         Quantity,         (Price * Quantity) as TotalFROM Table1;

Use this code to display all of the records in your table, with automatically calculated Total:

<%Dim RS, Conn, DSNName, SQLSQL = "SELECT ID, Item, Price, Quantity, (Price * Quantity) as Total FROM your_table;"DSNName = "Driver={Microsoft Access Driver (*.mdb)};Dbq=database_path;" & _          "Uid=database_username;Pwd=database_password;"  'leave these alone                                                          'if your database isn't password protectedSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.RecordSet")Conn.Open DSNNameRS.Open SQL, Conn, 1, 1%>    <table>        <tr>             <th>ID</td>             <th>Item</td>             <th>Price</td>             <th>Quantity</td>             <th>Total</td>        </tr><%    Do until rs.eof        response.write "<tr>"        response.write "<td>" & RS("ID") & "</td>        response.write "<td>" & RS("Item") & "</td>        response.write "<td>" & RS("Price") & "</td>        response.write "<td>" & RS("Quantity") & "</td>        response.write "<td>" & RS("Total") & "</td>        response.write "</tr>"        rs.Movenext    Loop%>    </table><%Conn.CloseRS.CloseSet Conn = nothingset RS = nothing%>

The output for that script will look somewhat like this:

ID      Item    Price   Quantity    Total1       Cat     $12.00  11          $132.002       Bunny   $6.99   13          $90.873       Mouse   $3.99   45          $179.554       Dog     $16.95  7           $118.655       Horse   $34.99  52          $1,819.48

I don't recommend using Access databases. Use MySQL or SQL Server (if you choose to use one of those databases, then the same code above will work, only you'd have to use a different connection string).

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...