Create Calculation Expression in Access 2000 [Answered]RSS

7 replies

Last post Jun 07, 2019 03:14 AM by Jalpa Panchal

  • Create Calculation Expression in Access 2000

    Jun 03, 2019 05:02 PM|gcqji|LINK

    Hi. I have a legacy FrontPage 2000 website that uses an Access 2000 database (Access2000test.mdb)

    see information in Shared One-Drive:
    https://1drv.ms/u/s!AkVNIj06hxI7gRjXjIxAS0bPBt6b

    1- In table1, I have created two fields where I would like to calculate the following as each record is created

    [TradePriceTotal} I would like this to calculate the total sum of the field [TradePrice] for all records

    [TradePriceAvg] I would like this field to calculate the avg of [TradePrice] for all records

  • Re: Create Calculation Expression in Access 2000

    Jun 04, 2019 02:12 AM|Jalpa Panchal|LINK

    Hi,

    Do you want as below or store that value in the table also?

    <html>
      <head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      </head>
    <body> 
    
    <%
    Dim objConn
            Set objConn = Server.CreateObject("ADODB.Connection")
            objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\aspsamplesite\Access2000Test.mdb;"
            objConn.Open
           
        
            Set str = objConn.execute("SELECT sum(TradePrice)as totalprice,avg(TradePrice)as averageprice FROM Table1;")
           
            Response.Write("<table border=1>")
              Response.Write "<tr><td> totalprice </td><td> avg </td></tr>"
                  If str.BOF And str.EOF Then
                  ' No data
                  Else
                  Do While (Not str.EOF)
                  Response.Write "<tr><td>" & str("totalprice") & "</td><td>" & str("averageprice") & "</td></tr>"
                  str.MoveNext
                  Loop
                  End If
                  
          Response.Write("</table>")
    %>
    
    </body>
    </html>

    Regards,

    Jalpa

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue.
    If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  • Re: Create Calculation Expression in Access 2000

    Jun 04, 2019 03:49 AM|gcqji|LINK

    Jalpa:

    If there is a formula/expression entered within a table that calculates the sum (or avg) of all records for a specific field within the same table, will that value not be able to simply show up as a field result within Access itself? (perhaps within the last record entered).

    If the calculated field/expression can be added to a query or contained within the table itself, and if I was simply using Access on its own, should I not be able to view the field's calculated result by opening the

    If I have to enter all or part of the above code on the .asp page itself, can you highlight the areas that would vary with a different record source, different named fields, etc.?

    Hard for me to decipher the above, as I am using the FrontPage2000 Wizard to create the .asp page, and I'm not sure how to use the information above.

    I thought that these expressions would be entered and performed by Access itself either as part of a table or a query... no?

    Ron

  • Re: Create Calculation Expression in Access 2000

    Jun 04, 2019 06:05 AM|Jalpa Panchal|LINK

    You could also run a query in access database as below:

    SELECT Table1.ID, Table1.Name, Table1.TradePrice, (select Sum([TradePrice]) from Table1) AS TradePriceTotal,(select Avg([TradePrice]) from Table1) AS  TradePriceAverage
    FROM Table1;
    

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue.
    If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  • Re: Create Calculation Expression in Access 2000

    Jun 04, 2019 11:44 AM|gcqji|LINK

    Jalpal:

    I am unsure how to write the suggested statement in my Access 2000 query (TradeQuery)

    I have included a screenshot of what is not a correct statement for displaying the total sum of the filed "tradeprice1"

    Could you please type the exact wording for me to enter in the third column showing in the query?

    Updated OneDrive link:

    https://1drv.ms/f/s!AkVNIj06hxI7gRn8wrZoytZBpbr3



    Thanks,

    Ron

  • Re: Create Calculation Expression in Access 2000

    Jun 05, 2019 07:01 AM|Jalpa Panchal|LINK

    You could add like below:

    TradePriceTotal: (select Sum([TradePrice]) from Table1)
    TradePriceAverage: (select Avg([TradePrice]) from Table1)

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue.
    If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  • Re: Create Calculation Expression in Access 2000

    Jun 06, 2019 02:45 PM|gcqji|LINK

    Jalpal.

    Thank you . That worked.

    However, I read somewhere that a field can be created (using perhaps a similar statement) within a table that will provide the same function. In another database, I tried entering the same type of statement within a query, where I was trying use the "sum" statement with a calculation expression that was created within the same query, but it rejected the way I wrote the "sum" expression.

    In your example, the expressions  end with "from table1". This works when the expression includes an actual field from that table.

    For example, here are the following fields from the same table (CatCodes) that have been added into a query called "CatQuery"

    Cat1Cost
    Cat2Cost
    Cat3Cost

    To tabulate the total of the 3 fields within each record, I created the following expression/formula within the same query:

    CatTotalCost: [catonecost]+[cattwocost]+[catthreecost]

    Is there a way to word the "sum" expression  differently that would produce the total sum of all records for the query expression "CatTotalCost"?

    Ron

  • Re: Create Calculation Expression in Access 2000

    Jun 07, 2019 03:14 AM|Jalpa Panchal|LINK

    Hi,

    Your original post issue is solved so I suggest you mark the solution as an answer and ask a new question in a new post.

    Thank you for understanding.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue.
    If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.