How to multiply two fields in Microsoft Access 2007

I have an Access database with a table titled products.  I want to multiply two fields automatically.  One field contains the price of a product (pPrice), and the other field contains the amount of units remaining (plnStock).   I want the results to reside in a field (currency) titled pTotals.

What is the best way of accomplishing this? Can this be done automatically inside Access?  If so, how?  What specific steps are taken to accomplish this?

Thank you.
arendt73Asked:
Who is Participating?
 
shaydieConnect With a Mentor Commented:
Are you sure you want to store the calculated value in the table? you can do the calculation in a query at any time like this:


SELECT Products.plnStock, Products.pPrice, [plnStock]*[pPrice] AS pTotal
FROM Products;
0
 
arendt73Author Commented:
Yes, I want to store the calculation in a table field.  But by doing this, when the numbers change, will this automatically adjust?

And if this can be accomplished, how do I do this inside Access?

Thank you.
0
 
Gustav BrockCIOCommented:
> .. by doing this, when the numbers change, will this automatically adjust?

No. That's why you will use a query instead as shaydie explained.

/gustav
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"But by doing this, when the numbers change, will this automatically adjust?"
No ... however, there is a new feature in A2010 (maybe A2007) that does something like this ...but definitely not in A2003 or prior.

Also ... a slight modification to saydie's query ... to take into account Null values ... and avoid #Error:

SELECT Products.plnStock, Products.pPrice, Nz([plnStock],0) * Nz([pPrice],0) AS pTotal
FROM Products;

mx
0
 
answer_dudeCommented:
What you're describing is a calculated field residing in a table.  The answer is if you want to do that you'll need to go through some hoops -- either using code on forms -- or using an update query run as often as data changes in order to maintain the value.

There is a very good discussion about Calculated Fields here and what your options are...

http://allenbrowne.com/casu-14.html
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes, Allen points out the feature I eluded to in A2010 is not a good idea.

mx
0
 
Kuwait10Commented:
Hi, you can upgrade to Access 2010 to use the calculated fields in tables.
All the results will be updated, unless you change the expression in the field properties.
Good luck.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Kuwait10: ... May I suggest you read the link above >> http://allenbrowne.com/casu-14.html

mx
0
 
Kuwait10Commented:
DatabaseMX: Thanks for sharing, yes the best practice to calculate fields is in queries level, but also we can get benefit from the new features in Access 2010.
I test the calculated field before and it is works fine with me, even I change the expression many times, and the results still updated.  I hope you to discover this great feature soon
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I hope you to discover this great feature soon"
I would never use that feature.  I think you may have missed the important part of Allen's post on this subject:

"Even more serious, the calculated results are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (through new records are edits are updated), so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results.

Since you cannot index calculated fields anyway, there is no performance benefit to be gained from using them. You are violating fundamental rules of data normalization for no good purpose. We recommend you use queries for calculated fields, just as in previous versions. "

Allen is well respected in the Access community. The information on his site is Time Tested.

mx
0
 
Patrick MatthewsCommented:
Kuwait10,

While it is pleasing to hear that your experience has been good, like MX I think we would be doing our Askers a disservice to recommend using that feature.

I fancy myself a very knowledgeable Access Expert, and yet Allen Browne has forgotten more about Access than I am ever likely to know.  If he says that, in his experience, the feature is less than 100% reliable, I am inclined to believe him.

To the extent that it is reasonable, we should be trying to inculcate best practices, and using calculated fields is not a best practice.  (Neither, in my opinion, are those new multi-valued fields that debuted in Access 2007.  I only recommend using them if the database will at some point be deployed in SharePoint.)

Cheers,

Patrick
0
 
arendt73Author Commented:
I have taken all of the experts recommendations, especially shaydie's, and decided that a query was the way to go.  But I am having an issue with computing multiple records on a page.

Please see attached code and screen shot (current).  The screen shot reflects the current page, multiplying the first line item only.  It is not multiplying the other items (stamps).

I do have a repeat region in the table.  Could this be causing the issue? And if so, what is the solution?

Also attached is the code.  Any assistance with the code is greatly appreciated.

Thank you. current
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\stampsjax\fpdb\vsproducts.mdb;"

Recordset1.Source = "SELECT * FROM Products ORDER BY pPrice ASC"

Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\stampsjax\fpdb\vsproducts.mdb;"

Recordset2.Source = "SELECT Products.pInStock, Products.pPrice, [pInStock]*[pPrice] AS pTotal FROM Products; "

Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>
 
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>

<html>
<head>
<title>Stamps</title>
<LINK REL=STYLESHEET TYPE="text/css" HREF="style.css">
</head>

<body bgcolor="#FFFFFF" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<table width="62%" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#0099CC">
  <tr>
    <td><div align="center"><img src="images/PostageSmall.gif" width="500" height="50"><br>
        Current Stock of Postage Stamps as of:<br>
</div></td>
  </tr>
  <tr>
    <td>
	<% 
	While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) 
   	%>
      <table width="500" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr> 
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
        <tr> 
          <td width="167"><strong><%=(Recordset1.Fields.Item("pName").Value)%></strong></td>
          <td width="166">Current Stock : <%=(Recordset1.Fields.Item("pInStock").Value)%></td>
          <td width="167"><%=(Recordset2.Fields.Item("pTotal").Value)%></td>
        </tr>
        <tr> 
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table>
	  <%
  	 	Repeat1__index=Repeat1__index+1
  		Repeat1__numRows=Repeat1__numRows-1
  		Recordset1.MoveNext()
  		Wend
		%>
    <p>&nbsp;</p>
      <p>&nbsp;</p></td>
  </tr>
</table>
</body>
</html>

Open in new window

0
 
Gustav BrockCIOCommented:
You should use Recordset2 only:

          <td width="167"><strong><%=(Recordset1.Fields.Item("pName").Value)%></strong></td>
          <td width="166">Current Stock : <%=(Recordset1.Fields.Item("pInStock").Value)%></td>
          <td width="167"><%=(Recordset2.Fields.Item("pTotal").Value)%></td>

/gustav
0
 
arendt73Author Commented:
What do you mean? Are you saying that Recordset2 should read as:

SELECT Products.pName, Products.pInStock, Products.pPrice, [pInStock]*[pPrice] AS pTotal FROM Products; ORDER BY pPrice ASC
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Yes, or just:

SELECT *, [pInStock]*[pPrice] AS pTotal FROM Products ORDER BY pPrice ASC;

/gustav
0
 
arendt73Author Commented:
I applied the suggestion and retrieved some of the results I need.  Thank you cactus_data.  Please see screen shot.  What can I add to ensure that pTotal appears as currency?  Meaning, adding a $ and appropriate zeros to pTotal? screenshot
0
 
arendt73Author Commented:
Found the answer to my question.

SELECT *, Format([pInStock]*[pPrice],'currency') AS pTotal FROM Products ORDER BY pPrice ASC
0
 
Gustav BrockCIOCommented:
Exactly.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.