add formula to a field on the form based on other fields

[cyril.casia]

Hello All,

I'm new to db and trying to make a database of the memberships in my office.

Now I have a table "tblTradingAnalysis" which has fields such as........(I have attached the screenshot of the tblTradingAnalysis for better understanding)

Now I have created a Form "frmTradingAnalysis" for the user to enter the data.
We would need the user to input only 6 fields such as ClientID, TradingDate, Product, Unit, SELL Price, BUY Price and the remaining fields must be automatically calculated as these are based on the values of the above 6 fields.
The remaining need to display the result and also need to get saved in the particular field in the table... Is this possible???? If not, what is the other best option... Please guide me as I'm unable to do it myself...

Any help would be appreciated!!!!

Regards
Cyril

 

[cyril.casia]

Forgot to mention the calculations to be done.... Here it is:

NET Value = (BUY Price * 5) + (SELL Price * 5)

Weight has to be automatically added as each product has particular weight. I have added a combobox for the users to select the product and added the values in the Row Source and its working fine. Now when the user selects a product from the list, the Weight listbox needs to get updated automatically.
Example:
Product Weight
Gold 100
Silver 10
Copper 1000
Lead 5000
(How can I do this?????)

Brokerage: (0.01% of NET Value) + 12.3% of (0.01% of NET Value)

Gross P&L: (Difference in BUY Price & SELL Price) * Weight

Net P&L: Gross P&L - Brokerage

TOTAL: By default it is 50,000 in the beginning of a membership and then on every transaction, the NET P&L is added to it and it goes on....

I know its lot to understand..... but i'm stuck up with the formula part.. I do not know how and where to put it... Please help.....

 

[plog]

Its possible, but its not the right way. You should not save data which is derived/calculated from data already in your table. You would obtain this data by creating a query and building custom fields for those fields which are calcualted.

First though, you need to reconfigure your data so that you can do math without unexpected errors. I don't see a single numeric field in your table--they are all text values. If this data is to hold numeric data, make it numeric data. Either use currency, double or Long Integer field types for this data. While you're at it, rename your fields to eliminate spaces (i.e. [BUY Price] becomes [BuyPrice], etc.)

Once that is done, you can start building the query you need. As a starter, this is the SQL for determining NetValue:

SELECT BuyPrice, SellPrice, (BuyPrice + SellPrice)*5 AS NetValue FROM tblTradingAnalysis;

Once you get a handle on this, we can tackle the Product Weight which will require another table.

 

[cyril.casia]

Thanks for your reply...
I have made the changes in the field name... but I'm confused as to where to add the code which you gave.
I have made a query for the tblTradingAnalysis. Now do I have to open the query on the SQL View and add this code there?

 

[plog]

Yes, paste my code into SQL view, run it to make sure it works, then open it in design view so that you can see how it is working and extrapolate how to create the other custom calculated fields you need.

 

[cyril.casia]

When I open it in the SQL view, a code already exists there:
SELECT tblTradingAnalysis.TransactionID, tblTradingAnalysis.ClientID, tblTradingAnalysis.TradingDate, tblTradingAnalysis.Product, tblTradingAnalysis.BUYPrice, tblTradingAnalysis.SELLPrice, tblTradingAnalysis.NETValue, tblTradingAnalysis.WEIGHT, tblTradingAnalysis.UNIT, tblTradingAnalysis.[GROSSP&L], tblTradingAnalysis.BROKERAGE, tblTradingAnalysis.[NETP&L], tblTradingAnalysis.[TOTAL BALANCE]
FROM tblTradingAnalysis;

Now if I add the given code below of this and run, it shows an error stating:
Character found after end of SQL statement

 

[plog]

Start a new query, open it in SQL view and then paste my code.

 

[cyril.casia]

Hai.... I got it... Its working now... How shall I go ahead with the other fields...
Do I have to create a new query for every field this way?

 

[cyril.casia]

I have now got the brokerage calculated on the same query by adding the formula to it in the Design view.

One thing which I forgot to mention, The above formula for the NetValue needs to be changed. I had mentioned it as:
NetValue = (BuyPrice*5) + (SellPrice*5)
Now there is a change in this:
Net Value = (BuyPrice + Sell Price)*Weight*Unit

For this we need to first work with the Weight. I have attached the Weight for the Products where when the user updates the form with the product, the weight must be auto populated. Please advice how to do this.....

 

[plog]

The product weights need to become a table that looks very similar to that spreadsheet and call it something like Products. The difference being a field called something like ProductID which is an autonumber field and will be the primary key of every product.

Next, you would add a numeric ProductID field to tblTradingAnalysis that will replace the current Product field you have. Then you would update each record of tblTradingAnalysis so that the ProductID field is populated with the correct number of each product. This can be down by building a query, bringing in the tblTradingAnalysis and Products table, linking the Product fields in each table, brining down the ProductID field of the tblTradingAnalysis table and updating it to the ProductId of the Products table.

Last, you would bring in the Products table into the design view of the query you have that has your calculations in it, linking it to tblTradingAnalysis in the same manner (ProductID to ProductID). This will give you access to the Weight of each Product and allow you to do the rest of your calculations.