Error Copying Field Data use =Sum([]) (1 Viewer)

access2010 · Mar 10, 2019

Hello, using Ms Access 2003 we would like to copy 2 fields

SharesOwned by using =Sum([TransactionQuantity])

Net_Share_Cost by using =Sum([AverageShareCost])

But errors keep occurring.

A suggestion would be appreciated.

Thank you,
Nicole

essaytee · Mar 10, 2019

Try this in the OnCurrent event of the parent form for the Quantity field:

Code:

Me.Net_Share_Quantity = DSum("[TransactionQuantity]", "Investments_Purchases_SalesT", "Symbol_Stock = '" & Me.Symbol_Stock & "'")

Apply the same method for the Cost field.

There are other ways to achieve this but in the interim, this is something for you to play around with.

Edit: If doing the above, remove the control source from the parent quantity textbox.

CJ_London · Mar 10, 2019

your transactionquantity is in a subform, you need to put your control in the header of footer of the subform.

however you already have that calculation in a control called SharesOwned, so refer to that instead

=[Investments_Purchases_SalesF_SubForm].[Form].[sharesowned]

access2010 · Mar 10, 2019

Thank you for your suggestions.

We have used the following code with success.
=[Investments_Purchases_SalesF_SubForm].[Form].[sharesowned]

I/We do appreciate your help.
Paul

access2010 · Mar 10, 2019

CJ_London
Thank you for your code which is working.

For some reason the data = Net_Share_Quantity = and =Net_Share_Cost = are not being saved into our table to be used in other forms.

Could you please advise me as to what I have done wrong?

Thank you
Paul

essaytee · Mar 11, 2019

access2010 said:
CJ_London
Thank you for your code which is working.

For some reason the data = Net_Share_Quantity = and =Net_Share_Cost = are not being saved into our table to be used in other forms.

Could you please advise me as to what I have done wrong?

Thank you
Paul

You have a design issue problem. I can see that in your primary investment table you have fields for NetShareQuantity and NetShareCost but you don't really need those fields in any table.

Simply calculate them, as you do for the main and subforms the subject of this thread. Where you require that information for other forms, base those other forms on queries, in those queries you calculate that information.

Another approach, utilise the OnCurrent method I mentioned previously (3 or 4 posts above). If doing this approach, with the query you wouldn't also calculate the same information.

I've only honed in on the two fields you've mentioned, I haven't analysed the rest of the fields in your tables.

access2010 · Mar 14, 2019

essaytee
Thank you for your suggestion, but we are all volunteers at a NGO (Charity) and can not figure out how to create the code which you have suggested.
Would you be kind enough to place Your Code into the data base that we have uploaded.

Thank you.
Crystal

essaytee · Mar 14, 2019

access2010 said:
essaytee
Thank you for your suggestion, but we are all volunteers at a NGO (Charity) and can not figure out how to create the code which you have suggested.
Would you be kind enough to place Your Code into the data base that we have uploaded.

Thank you.
Crystal

There is always more than one way to skin a cat. To answer your question, here is one way. I created three queries, each one builds upon the preceding one.

Query 1: (qryZ_01_CalculateLineTotals)

Code:

SELECT Investments_Purchases_SalesT.Symbol_Stock, Investments_Purchases_SalesT.TransactionQuantity, Investments_Purchases_SalesT.TransactionPrice, Investments_Purchases_SalesT.TransactionComm, ([TransactionQuantity]*[TransactionPrice])+[TransactionComm] AS LineTotalAmount
FROM Investments_Purchases_SalesT
ORDER BY Investments_Purchases_SalesT.Symbol_Stock;

This query includes one calculated field (LineTotalAmount) (I used the prefix Line, so I know I'm dealing with an acutal transaction line).

Query 2: (qryZ_02_LineItems) Is based upon Query 1 above:

Code:

SELECT qryZ_01_CalculateLineTotals.Symbol_Stock, Sum(qryZ_01_CalculateLineTotals.TransactionQuantity) AS StockQuantity, Sum(qryZ_01_CalculateLineTotals.LineTotalAmount) AS StockTotalPrice
FROM qryZ_01_CalculateLineTotals
GROUP BY qryZ_01_CalculateLineTotals.Symbol_Stock;

This query (a Totals query) merely sums the stock quantity and LineTotalPrice. LineTotalPrice was a calculated field from Query 1.

Query 3: (qryZ_03_StockAvgPrice) Is based upon Query 2 above:

Code:

SELECT qryZ_02_LineItems.Symbol_Stock, qryZ_02_LineItems.StockQuantity, qryZ_02_LineItems.StockTotalPrice, IIf([StockQuantity]>0,[StockTotalPrice]/[StockQuantity],0) AS NetStockAvgPrice
FROM qryZ_02_LineItems;

This query is required in order to extract and correctly calculate the average price per share, for what balance is remaining of a stock.

You will be able to copy the above queries directly into your db for testing, as long as you name them as I have indicated (qryZ_....). The only reason I named them like that is to keep them together at the end of the query list in your db.

access2010 · Mar 19, 2019

essaytee
Thank you for your assistance.
I think that I have done what you have suggested, BUT, no results are appearing.
Could you please advise me as to what I have done wrong, or could you please fix the attached data base so that it will work.
I/We do appreciate your assistance.
Crystal, Thank You

essaytee · Mar 19, 2019

access2010 said:
essaytee
Thank you for your assistance.
I think that I have done what you have suggested, BUT, no results are appearing.
Could you please advise me as to what I have done wrong, or could you please fix the attached data base so that it will work.
I/We do appreciate your assistance.
Crystal, Thank You

What are you trying to achieve? I think you have misinterpreted the use and purpose of the three queries I provided. You haven't altered the form to suit those queries and incorrectly added three fields (that correspond to the three queries I provided) to your table. You already have two text boxes (Net Share Quantity & Net Share Cost) that correctly display the data. I compared the results of the query I provided against your existing form, the results are identical; so to my first question, what are you trying to achieve?

What you could do, is remove from the Parent form "Net Share Quantity" and "Net Share Cost" as that information is duplicated, you have it displayed in the sub-form.

As an aside, what is NGO? What do they do?

isladogs · Mar 19, 2019

NGO = non governmental organisation which provides services e.g. charity

access2010 · Mar 22, 2019

essaytee
Thank you for your note.
Most of the staff in our office are Volunteers and our Director would like to see the, "Net Share Quantity" and "Net Share Cost" on the Main Browse form and on other forms and in reports.

Our difficulty is that EVERY December we receive MANY DIFFERENT EQUITIES as donations and the donors receive tax receipts for their donations. Some of the equities are duplicates and when our organization needs funds we sell some equities.

At this time we would like to have these values visible on different forms and reports.

Any assistance you can provide will be appreciated.
Nicole

essaytee · Mar 23, 2019

access2010 said:
essaytee
Thank you for your note.
Most of the staff in our office are Volunteers and our Director would like to see the, "Net Share Quantity" and "Net Share Cost" on the Main Browse form and on other forms and in reports.

Our difficulty is that EVERY December we receive MANY DIFFERENT EQUITIES as donations and the donors receive tax receipts for their donations. Some of the equities are duplicates and when our organization needs funds we sell some equities.

At this time we would like to have these values visible on different forms and reports.

Any assistance you can provide will be appreciated.
Nicole

I'm not sure how much headway we can make here. It seems like you are struggling with the underlying table structure (my three queries, case in point). By headway, I mean to continue with form design and implementation based on current table design.

To address the table design that will require some concerted study on your part. It is a dry topic, but when you get your head around it, other things fall into place. This is a good starting point.

To address directly your question that you could not get my queries to work, this is how they could be implemented.

Query number 3 (qryZ_03_StockAvgPrice) is used to create another query (qryZ_04_StockSummary) that will be the recordsource of your parent form (yours copied to sat_Investments_Purchases_SalesF). In that parent form, the 'Net Share Quantity' and 'Net Share Cost' have Control Sources directly from the forms recordsource.

If you check the query (qryZ_04_StockSummary) that is the recordsource of the form, you will see that, if you wanted to, you could add other relevant fields from your Investment table. (I've attached your db)

As previously mentioned, I've named the queries and form in that style for ease of my reference.

access2010 · Mar 24, 2019

essaytee
Thank you for the link to the article.

We are still trying to understand how to obtain the information that our Director wants, which we are still working on to-day.
===

Could a Update Query as you suggested to Crystal, which works for her problem, be used to copy the "Net Share Quantity" and the "Net Share Cost" onto the Main Browse form and onto other forms and in reports?

Thank you
Nicole

essaytee · Mar 25, 2019

access2010 said:
essaytee
Thank you for the link to the article.

We are still trying to understand how to obtain the information that our Director wants, which we are still working on to-day.
===

Could a Update Query as you suggested to Crystal, which works for her problem, be used to copy the "Net Share Quantity" and the "Net Share Cost" onto the Main Browse form and onto other forms and in reports?

Thank you
Nicole

Did you check the form, "sat_Investments_Purchases_SalesF" that is a copy of your form, prefixed with "sat_". The ''Net Share Cost' and 'Net Share Quantity' (or Avg Price, can't recall at the moment) are already displayed on your main browser form, they were calculated, that is what I provided above. I don't understand why they need to be copied, copied to where? Where ever that information is required, calculate it.

An Update Query updates data in tables, is this what you mean by copying the data? I'm suggesting you shouldn't store calculated fields in tables. If you really want to take this approach, from your other thread, simply follow along and substitute the various Form and Field references to suit.

I haven't got access to you db at the moment, so can't check it in detail.

access2010 · Mar 28, 2019

essaytee = 99%
Thank you for your suggestions which work and we have one problem that requires an adjustment.
===
Query Required for Calculation

===
We would like to create a query to find our {Average Share Cost} as the calculation we had been using is not acceptable with our accountant.

We should find the total $ value of all {Bought_$} plus all {DRIP} transactions and then divide their total $ by the number of {Shares Owned}

Thank you,
Crystal

essaytee · Mar 29, 2019

access2010 said:
essaytee = 99%
Thank you for your suggestions which work and we have one problem that requires an adjustment.
===
Query Required for Calculation

===
We would like to create a query to find our {Average Share Cost} as the calculation we had been using is not acceptable with our accountant.

We should find the total $ value of all {Bought_$} plus all {DRIP} transactions and then divide their total $ by the number of {Shares Owned}

Thank you,
Crystal

You'll have to explain to me what is 'Drip' when batched together with 'Bought_$ and Sold_$.

I've perused your latest db but can't do anything useful with it at the moment.

essaytee · Mar 29, 2019

Try this for starters.

You need to edit Query 1 (qryZ_01_CalculateLineTotals) by adding in the field, "TransactionType". In the Criteria columns, enter "Bought_$" and in the same column below, enter "Drip". That will restrict the query to only retrieve records marked as "Bought_$" or "Drip".

The full query is as follows:

Code:

SELECT Investments_Purchases_SalesT.Symbol_Stock, Investments_Purchases_SalesT.TransactionQuantity, Investments_Purchases_SalesT.TransactionPrice, Investments_Purchases_SalesT.TransactionComm, ([TransactionQuantity]*[TransactionPrice])+[TransactionComm] AS LineTotalAmount, Investments_Purchases_SalesT.TransactionType
FROM Investments_Purchases_SalesT
WHERE (((Investments_Purchases_SalesT.TransactionType)="Bought_$")) OR (((Investments_Purchases_SalesT.TransactionType)="Drip"))
ORDER BY Investments_Purchases_SalesT.Symbol_Stock;

Copy the above and enter it into the SQL View of Query 1.

Does this get close to the figures you're after?

As an aside, I noticed that the TransactionQuantity per line is either positive or negative. The negative entries relate to 'Sold_$'. Without directly referring to the Transaction Type (Bought, Sold, Drip) it would be possible, in the query, to only restrict entries where the TransactionQuantity > 0 (must be either Bought or Drip (what ever Drip is)).

access2010 · Mar 30, 2019

essaytee
Drip (Dividend Reinvestment Plan) = www.investopedia.com/terms/d/dividendreinvestmentplan.asp
Our NGO finds that using Drips increases our Endowment's growth by not having to pay brokerage commission for new share purchases.
===
Our Volunteers sign on for Monthly intervals.
The people who had been working with you on this project have now left.
Could you please place your queries into the forms that you have in our uploaded database as I/we are not a programmers and upload the edited Data Base?
===
Yes, correct the query and thank you for your suggestion=
restrict entries where the TransactionQuantity > 0 (must be either Bought or Drip (what ever Drip is)).
===
Fabiola

Gasman · Mar 30, 2019

If I am speaking out of turn here, I am sure I will be admonished.

However PMFJI,

I have helped and tried to help you in the past, but NGO or not, you need someone who stays for the duration.

You cannot expect people here to retrain users each month or months on aspects of Access that have already been explained.?

I happy to say this, as I am also volunteer for a charity, but I am prepared to stay with it until circumstances change dramatically for me, and I do not expect others to do the work for me.? I will ask for assistance obviously, but not repeat the request.

There is asking for help, and then there is just taking plainly taking advantage.

I feel you are now doing the latter.?

My 5 cents as the USA will say.

Error Copying Field Data use =Sum([]) (1 Viewer)

Registered User.

Attachments

Need a good one-liner.

Super Moderator

Registered User.

Registered User.

Attachments

Need a good one-liner.

Registered User.

Need a good one-liner.

Attachments

Registered User.

Attachments

Need a good one-liner.

MVP / VIP

Registered User.

Need a good one-liner.

Attachments

Registered User.

Need a good one-liner.

Registered User.

Attachments

Need a good one-liner.

Need a good one-liner.

Registered User.

Enthusiastic Amateur

Similar threads

Users who are viewing this thread