IIS 7 and Above
Create Calculation Expression in Access 2000
Last post Jun 07, 2019 03:14 AM by Jalpa Panchal
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:
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
Jun 04, 2019 02:12 AM|Jalpa Panchal|LINK
Do you want as below or store that value in the table also?
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\aspsamplesite\Access2000Test.mdb;"
Set str = objConn.execute("SELECT sum(TradePrice)as totalprice,avg(TradePrice)as averageprice FROM Table1;")
Response.Write "<tr><td> totalprice </td><td> avg </td></tr>"
If str.BOF And str.EOF Then
' No data
Do While (Not str.EOF)
Response.Write "<tr><td>" & str("totalprice") & "</td><td>" & str("averageprice") & "</td></tr>"
Jun 04, 2019 03:49 AM|gcqji|LINK
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
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?
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
Jun 04, 2019 11:44 AM|gcqji|LINK
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:
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)
Jun 06, 2019 02:45 PM|gcqji|LINK
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"
To tabulate the total of the 3 fields within each record, I created the following expression/formula within the same query:
Is there a way to word the "sum" expression differently that would produce the total sum of all records for the query expression "CatTotalCost"?
Jun 07, 2019 03:14 AM|Jalpa Panchal|LINK
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.