Copy field and close form (1 Viewer)

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
Using Ms Access 2003 we would like to copy the calculated data in the field
"AverageShareCost" to the the field "ShareCost_Average"
and then close the form, using the Green Arrow Bmp Button

Your suggestion, will be appreciated.

Thank you,
Nancy
 

Attachments

  • Investments_Purchases_SalesF_Copy_Average_Cost.mdb
    592 KB · Views: 116

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
bob fitz
Thank you for your note, we tried creating the calculation as we need it without any success.
We need the "ShareCost_Average" cost, when we create our CVS file.

Do you have a suggestion as to how we can create this value weekly other then copying the value?

Thank you.
Paul
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
You can add it to your query as a function

Code:
Public Function GetShareAvg() As Currency
  Dim rs As DAO.Recordset
  Dim TranPrice As Currency
  Dim TranQuan As Long
  TranPrice = DSum("[TransactionQuantity]*[TransactionPrice]+[TransactionComm]", "QryInvestments_Purchases_Sales")
  TranQuan = DSum("[TransactionQuantity]", "QryInvestments_Purchases_Sales")
  If TranQuan <> 0 Then
    GetShareAvg = TranPrice / TranQuan
  End If
End Function

This works for the provided data, but it is using all the records. My guess is you want to do this by some group of records. I would have thought by portfolio, but there are 3 portfolios in your dataset and you use all of them. However, it can be done by group.

Now get Rid of the field in the table, and add the function to your query like
ShareCost_Avg:GetShareAvg()
 

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
MajP

Thank you for your suggestion which we have tried without success.

Can you please look at the similar code that we used in the past to copy a field and with your assistance, hopefully we can have the Copy Icon work and then save the record and close the form.

The Main form to open is "Investments_Purchases_SalesF"

Thank you,
Nicole
 

Attachments

  • Copy_Average_Equity_Cost.mdb
    376 KB · Views: 124

bob fitz

AWF VIP
Local time
Today, 09:35
Joined
May 23, 2011
Messages
4,717
What is the name of the field to be copied and what is the name of the field that it is to be copied to and in what table will these fields be found?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
Thank you for your suggestion which we have tried without success.

Can you please look at the similar code that we used in the past to copy a field and with your assistance, hopefully we can have the Copy Icon work and then save the record and close the form

The code I provided was tested and worked in the database.

See the last column of the query and the function in module Junk.
 

Attachments

  • Copy_Average_Equity_Cost_MajP.mdb
    1.5 MB · Views: 108

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
bob fitz
Thank you for your note.

What is the name of the field to be copied = AverageShareCost
Table = this is a calculated field

what is the name of the field that it is to be copied to = AverageShareCost
Investments01_tbl

Thank you,
Paul
 

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
MajP

Thank you for your suggestion.
We must have entered your code incorrectly as we did not have success.
We previously used the Copy Icon that is on the form which we have uploaded and would like to adjust this copy and close command, if you can assist us.

Linda
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:35
Joined
May 21, 2018
Messages
8,463
Paul and Linda,
We can provide a solution to copy a calculated value into a table, but we are trying to steer you away from that. Calculated fields are not normally stored for many reason. Mainly the value is only as good as the instance you enter it. After that any changes in data will not be synchronized. For a financial database, I would find this very concerning. We are suggesting that you do not copy it into a field, but show the value when needed in a query you can then export the query if needed. I can provide the code, with the understanding this is not normally done this way.

Here are some simple queries that can show the data whenever you want. Any changes in the data will be reflected in the calculations

Code:
SELECT 
  Investments_Purchases_SalesT.Symbol_Stock, 
  Investments_Purchases_SalesT.TransactionQuantity, 
  Investments_Purchases_SalesT.TransactionPrice, 
  Investments_Purchases_SalesT.TransactionComm, 
  [TransactionQuantity] * [transactionPrice] + [transactioncomm] AS TransactionTotal, 
  [TransactionTotal] / [TransactionQuantity] *(
    [transactionquantity] / Abs([transactionquantity])
  ) AS AverageCost 
FROM 
  Investments_Purchases_SalesT 
ORDER BY 
  Investments_Purchases_SalesT.TransactionDate;

Produces

Code:
Symbol_Stock	TransactionQuantity	TransactionPrice	TransactionComm	TransactionTotal	AverageCost
VG	-600.00		$4.22		$160.00		($2,371.94)	-3.95323333333333
VG	400.00		$5.45		$9.99		$2,189.99	5.474975
VG	250.00		$5.70		$9.99		$1,434.97	5.73986
VG	800.00		$6.43		$125.15		$5,269.95	6.5874375
VG	-650.00		$6.31		$10.09		($4,088.16)	-6.28947692307692
VG	200.00		$41.39		$160.00		$8,438.00	42.19
VG	-150.00		$37.66		$160.00		($5,489.00)	-36.5933333333333

Then you can use that in an group by query

Code:
SELECT qrytransactioncost.symbol_stock, 
       SUM(qrytransactioncost.transactionquantity) AS SharesOwned, 
       SUM(qrytransactioncost.transactioncomm)     AS TotalCommission, 
       SUM(qrytransactioncost.transactiontotal)    AS TransactionBalance 
FROM   qrytransactioncost 
GROUP  BY qrytransactioncost.symbol_stock;

This would give you

Code:
Symbol_Stock	SharesOwned	TotalCommission	TransactionBalance
VG		250		$635.22		$5,383.81

Then you can use that for the shares avg

Code:
SELECT qrytransactiontotals.symbol_stock, 
       [transactionbalance] / [sharesowned] AS AvgShareCost 
FROM   qrytransactiontotals 
GROUP  BY qrytransactiontotals.symbol_stock, 
          [transactionbalance] / [sharesowned];

This gives you

Code:
Symbol_Stock	AvgShareCost
VG		21.53522

You can then link everything by Symbol_Stock like

Code:
SELECT qrytransactiontotals.symbol_stock, 
       qrytransactiontotals.sharesowned, 
       qrytransactiontotals.totalcommission, 
       qrytransactiontotals.transactionbalance, 
       qryavgcost.avgsharecost 
FROM   qrytransactiontotals 
       INNER JOIN qryavgcost 
               ON qrytransactiontotals.symbol_stock = qryavgcost.symbol_stock;

This would show

Code:
[CODE]Symbol_Stock	SharesOwned	TotalCommission	TransactionBalance	AvgShareCost
VG		250		$635.22		$5,383.81		21.53522
[/CODE]
 

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
Thank you for previously helping us.

We have created this Database “K_Updated_Copy_Cost_&_Quantity.Mdb” so that our Director could have her needed data.

A = we have added the button “K” which manually copies the data from “SharesOwned” into memory and then is manually placed into the field “Net_Share_Quantity”.

B = we have added the button “K” which manually copies the data from “AverageShareCost” into memory and then is manually placed into the field “Net_Share_Cost”.

What we would like assistance with is to Click the GREEN button and perform the functions “A” and “B” and then close the form.

I/We do appreciate all of your assistance.
Maria
 

Attachments

  • K_Updated_Copy_Cost_&_Quantity.mdb
    436 KB · Views: 91

Gasman

Enthusiastic Amateur
Local time
Today, 09:35
Joined
Sep 21, 2011
Messages
14,037
Is everyone in the office having a go at learning Access?

Plus asking the same question in two different threads is not going to help your case?

You will likely get the same/similar answers posted to each, with each poster being unaware that the query had already been answered.?
 
Last edited:

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
Gasman
Thank you for your note.
Most of the Volunteers, work from their home and I will take your advice and have Nicole in our office co-ordinate any problems.

Paul
 

mike60smart

Registered User.
Local time
Today, 09:35
Joined
Aug 6, 2017
Messages
1,899
Hi Maria

Well finally we are making progress

On the Main Form I placed 2 Unbound Textbox's

Textbox 1 I named Owned and it displays the Total of Shares Owned
Textbox 2 I named Average and it displays the Average Share Cost

The Green Command Button in the Form Header has an On Click event which copies the values to the required Controls

View attachment K_Updated_Copy_Cost_&_Quantity.zip
 

access2010

Registered User.
Local time
Today, 02:35
Joined
Dec 26, 2009
Messages
1,019
W.O.R.K.I.N.G
Thank you, ALL for your assistance.
Our director is using our data base to her satisfaction.
Nicole
 

Users who are viewing this thread

Top Bottom