Link to home
Start Free TrialLog in
Avatar of arendt73
arendt73

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of shaydie
shaydie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of arendt73
arendt73

ASKER

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.
Avatar of Gustav Brock
> .. 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
"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
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
Yes, Allen points out the feature I eluded to in A2010 is not a good idea.

mx
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.
Kuwait10: ... May I suggest you read the link above >> http://allenbrowne.com/casu-14.html

mx
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
"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
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
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. User generated image
<%
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

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
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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? User generated image
Found the answer to my question.

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

/gustav