Aggregate Query Difficulty

[access2010]

Could we please have assistance in using our = Investments_Purchases_AggregateQ = (which was made to remove duplicate stock codes) to produce a report from our form = Investments_Purchases_SalesF = when the button = Investments Purchases Sales Report = is clicked?

We are using Access 2003
Thank you Crystal

 

[Ranman256]

Either key the table so you can't get duplicates,
Or
Set the query property to not get duplicates:: select distinct...

 

[gemma-the-husky]

in all seriousness, what do you mean ... duplicate stock codes?

assuming you have an investments table, linked to other tables, the reason for seeing duplicates, is going to be that either
- the query isn't summarising the investments in the way you expect - eg, you get a different summary line for each date, but you don't realise that
- there are in fact duplicate investments in the investment table OR
- there are duplicate referenced records OR EVEN
- you have some null values messing up the summary

In any event, it's not enough (imo) to just get rid of the duplicates. You need to understand why you have the duplicates, as you don't want to be reporting the wrong totals based on wrong assumptions. If you are dealing with investments, it needs to be right.

one way of checking - if you report all investments, the grand total needs to be confirmable as the correct grand total - if it isn't you can't rely on a filtered report total being correct either.

 

[access2010]

Either key the table so you can't get duplicates,
Or
Set the query property to not get duplicates:: select distinct...

Ranman256, thank you for your note and I/We have tried to Set the query property to not get duplicates:: select distinct,​

But I have had no luck in doing so in our Access 2003 database.
I also tried to hide Duplicate records in our report, but this also did nor work.
Would you be so kind as to add your suggestion to our database for us?
Thank you,
Nicole

 

[access2010]

in all seriousness, what do you mean ... duplicate stock codes?

assuming you have an investments table, linked to other tables, the reason for seeing duplicates, is going to be that either
- the query isn't summarising the investments in the way you expect - eg, you get a different summary line for each date, but you don't realise that
- there are in fact duplicate investments in the investment table OR
- there are duplicate referenced records OR EVEN
- you have some null values messing up the summary

In any event, it's not enough (imo) to just get rid of the duplicates. You need to understand why you have the duplicates, as you don't want to be reporting the wrong totals based on wrong assumptions. If you are dealing with investments, it needs to be right.

one way of checking - if you report all investments, the grand total needs to be confirmable as the correct grand total - if it isn't you can't rely on a filtered report total being correct either.

gemma-the-husky, thank you for your note.​

We are using a Database that was created about 10 years ago and we are slowly changing its structure,
The results that we are receiving is suitable except for this report.
The totals in our form is correct but not in the report.
If you have an opportunity could you please correct our report for us as I/We have tried to fix this problem since last summer.
Thank you.
Nicole

 

[gemma-the-husky]

Which object names are giving the different answers?

 

[gemma-the-husky]

The query Investments_Purchases_Sales_RQ which is the source of the report is showing 135 rows.

This is the cross product of 9 and 15 items in each of your 2 tables. Is this what you expect, or not? It sounds wrong to me off hand.

 

[access2010]

Thank you, gemma-the-husky for your question.
The database that we are using is VERY old and was not constructed properly, but it does contain the correct information,
When the report = Investments_Purchases_Sales_R = is printed we should only see 15 records.
Where quantity are 1,2,3,4,5,6,7,8,9,10,11,12,13,14 and 15. each of these records should be shown once.
Your assistance will be appreciated.
Nicole

 

[gemma-the-husky]

I am not sure exactly how you mange purchases and sales, but because you are relating a stock id in one table to a stock id in the second table, you are getting a cross product. for each record in table A, you see every record in table B.

Now if, say table A represent purchases, and table B represents sales, and you are trying to compare the costs with the sales to establish the profit. then you need to structure the data in a different way to answer that question. It's not really a problem of an old database design. If you only buy and sell discrete packages of stocks, then you need a stock package ID as well as the stock ID, and you match Sale 1 to Purchase 1, and Sale 2 to purchase 2. But if Sale 1 might contain part of Purchase 1, and Part of Purchase 2 - and Sale 2 contains the rest of Purchase 2 and part of Purchase 3 - then you can't directly match the purchases and sales. If dividends are reinvested, you are likely to have small increments to the purchases table to represent the DRIPs.

You may be able to aggregate all the purchases to give a total and an average cost, and use the average cost as the purchase value for a sale, but I don't know how you have to report sales and costs, as the profits will affect CGT calculations.

You could have a union query with fields from the two tables in date order, so you can see the flow of purchases and sales to establish the trading results of each sale. If you do that, treat the sales as negative values as it's easier.

Maybe you ought to store the purchase value you are calculating and eliminating on the sales record, as you record each transaction. I know you can evaluate it from first principles, but this might be a case when it's easier to store it. Then you could report the sales without needing the purchases at all.

If you eliminate costs on a FIFO basis, then the value of the investments on hand should be the most recent investments, and the sum of eliminated costs plus current value should equal the total paid, which gives you a good check,

I assume this is something new you are doing, as you must have had this before. If the form does it correctly, see what the form is doing differently to the report.

There is another thread at present talking about valuing trading inventory (widgets, not investments) but it's the same issue. Manging inventory quantity is relatively easy. Managing inventory value is a whole different level of complexity. For each sale, you may need to evaluate which purchases were sold on a FIFO basis in order to evaluate the profit.

I don't know if this is a commercial thing or just to track your own investments, but if it's commercial then the issue of compliance must be pretty important, and you need to be sure you are doing things correctly.

 

[Pat Hartman]

As the others have pointed out, Symbol_Stock is not unique in either table. That is what is causing the "duplication". The tables as designed do not make sense and I have built portfolio management applications so I know a little something about the process. Given where you are, and what I think you might be looking for, I would start with two queries. qry1 will summarize Investments01_tbl on Symbol_Stock.

Select Symbol_Stock, Sum(Net_Share_Quantity) as qty, Sum(Net_Share_Quantity * Net_Share_Cost) as TotalBasis.
From Investments01_tbl
Group by Symbol_Stock

Then summarize the other table on Symbol_Stock and TransactionType

Select Symbol_Stock, TransactionType, Sum(TransactionQuantity) As TranQty, Sum(TransactionQuantity * Nz(TransactionPrice,0)) as TotalSaleAmt, Sum(TransactionComm) as TotalComm
From Investments_Purchases_SalesT
Group by Symbol_Stock, TransactionType;

Then join these two queries in a third query. Use the third query as the RecordSource for the report. That will return one or two rows for each stock depending on if you have only buys or buys and sells.

I have no idea if this is what you are looking for.

 

[access2010]

I am not sure exactly how you mange purchases and sales, but because you are relating a stock id in one table to a stock id in the second table, you are getting a cross product. for each record in table A, you see every record in table B.

Now if, say table A represent purchases, and table B represents sales, and you are trying to compare the costs with the sales to establish the profit. then you need to structure the data in a different way to answer that question. It's not really a problem of an old database design. If you only buy and sell discrete packages of stocks, then you need a stock package ID as well as the stock ID, and you match Sale 1 to Purchase 1, and Sale 2 to purchase 2. But if Sale 1 might contain part of Purchase 1, and Part of Purchase 2 - and Sale 2 contains the rest of Purchase 2 and part of Purchase 3 - then you can't directly match the purchases and sales. If dividends are reinvested, you are likely to have small increments to the purchases table to represent the DRIPs.

You may be able to aggregate all the purchases to give a total and an average cost, and use the average cost as the purchase value for a sale, but I don't know how you have to report sales and costs, as the profits will affect CGT calculations.

You could have a union query with fields from the two tables in date order, so you can see the flow of purchases and sales to establish the trading results of each sale. If you do that, treat the sales as negative values as it's easier.

Maybe you ought to store the purchase value you are calculating and eliminating on the sales record, as you record each transaction. I know you can evaluate it from first principles, but this might be a case when it's easier to store it. Then you could report the sales without needing the purchases at all.

If you eliminate costs on a FIFO basis, then the value of the investments on hand should be the most recent investments, and the sum of eliminated costs plus current value should equal the total paid, which gives you a good check,

I assume this is something new you are doing, as you must have had this before. If the form does it correctly, see what the form is doing differently to the report.

There is another thread at present talking about valuing trading inventory (widgets, not investments) but it's the same issue. Manging inventory quantity is relatively easy. Managing inventory value is a whole different level of complexity. For each sale, you may need to evaluate which purchases were sold on a FIFO basis in order to evaluate the profit.

I don't know if this is a commercial thing or just to track your own investments, but if it's commercial then the issue of compliance must be pretty important, and you need to be sure you are doing things correctly.

Thank you gemma-the-husky for your reply.
The calculations you have suggested with the drips will be preformed after we get our report working.
The broker gives us one transaction slip for each equity purchase or sale with the average stock cost (including commission) even if there are many individual transactions at different prices.
According to our accountant we average our costs with sales, drips and commissions at the end of each year.
We used to use the FIFO method but we changed to a year end cost method averaging.
Our forms are co-operating with ALL of our accounting requirements, but we have been unable to print this yearly transaction report 023=Investments_Purchases_Sales_R = which after working will have more calculations on it.​

I work at an NGO (Charity) and every thing meets are accountants needs, except supplying the transaction report 023=Investments_Purchases_Sales_R = for each individual equity.
I have consolidated the records that start with 023= which is the day of the year we have worked on this database.
Could you please use our attached database and show us how to Programmatically creating the table = 023=Investments01_tbl = as we have not had any results.
Thank you.
Nicole

 

[access2010]

As the others have pointed out, Symbol_Stock is not unique in either table. That is what is causing the "duplication". The tables as designed do not make sense and I have built portfolio management applications so I know a little something about the process. Given where you are, and what I think you might be looking for, I would start with two queries. qry1 will summarize Investments01_tbl on Symbol_Stock.

Select Symbol_Stock, Sum(Net_Share_Quantity) as qty, Sum(Net_Share_Quantity * Net_Share_Cost) as TotalBasis.
From Investments01_tbl
Group by Symbol_Stock

Then summarize the other table on Symbol_Stock and TransactionType

Select Symbol_Stock, TransactionType, Sum(TransactionQuantity) As TranQty, Sum(TransactionQuantity * Nz(TransactionPrice,0)) as TotalSaleAmt, Sum(TransactionComm) as TotalComm
From Investments_Purchases_SalesT
Group by Symbol_Stock, TransactionType;

Then join these two queries in a third query. Use the third query as the RecordSource for the report. That will return one or two rows for each stock depending on if you have only buys or buys and sells.

I have no idea if this is what you are looking for.

Thank you Pat Hartman.
I think that I/We have tried what you have suggested, for so long and we still can not get this report to work properly.
If you have an opportunity, could you please enter your query into our attached table?
Thank you.
Nicole

 

[gemma-the-husky]

I can't do this for you, but I also can't see how you can just calculate an average cost over the whole year.

If you buy stock in April, sell some in June, buy some more in July, sell some in September etc, then you can't really say the cost of the sales in June is the average cost of all the purchases in the year, can you? If you do that, you can't "bed and breakfast", I don't think.

I can't see how you can do this with a single query. You could re-evaluate the average cost after each stock purchase, which might help. Maybe you can get a query with all the transactions in date order, and then iterate them with code to get to the costs you want, however you decide to evaluate the cost. Maybe you also need a way to store the closing/starting position at the end of each financial year, otherwise you might be going a long way back each time to do this. This ought to work with or without drips, but you need to add the drips in timeous fashion. If you add a drip after a sale has taken place, the costs you use for the sale won't have been corrected for the drip. Indeed the sale may be for a larger quantity of stock than you have without the drip.

@Pat Hartman may have some advice, as she is clearly familiar with portfolio management.

 

[Pat Hartman]

@access2010, I took the time to actually use your OWN column names, can you really not bother to type them in yourself? We're here to help you, not to do work for you that you are getting paid to do. I don't even know if the queries I suggested will give you the results you want. If the queries generate errors, we'll try to fix them for you. If they don't give the correct results, you are going to have to describe what you want with business logic AND an example using the data you posted.

 

[access2010]

I can't do this for you, but I also can't see how you can just calculate an average cost over the whole year.

If you buy stock in April, sell some in June, buy some more in July, sell some in September etc, then you can't really say the cost of the sales in June is the average cost of all the purchases in the year, can you? If you do that, you can't "bed and breakfast", I don't think.

I can't see how you can do this with a single query. You could re-evaluate the average cost after each stock purchase, which might help. Maybe you can get a query with all the transactions in date order, and then iterate them with code to get to the costs you want, however you decide to evaluate the cost. Maybe you also need a way to store the closing/starting position at the end of each financial year, otherwise you might be going a long way back each time to do this. This ought to work with or without drips, but you need to add the drips in timeous fashion. If you add a drip after a sale has taken place, the costs you use for the sale won't have been corrected for the drip. Indeed the sale may be for a larger quantity of stock than you have without the drip.

@Pat Hartman may have some advice, as she is clearly familiar with portfolio management.

Thank you gemma-the-husky for your concern and I will have your idea presented to our Accountant for her opinion and will advise you of the answer.
Crystal

 

[access2010]

@access2010, I took the time to actually use your OWN column names, can you really not bother to type them in yourself? We're here to help you, not to do work for you that you are getting paid to do. I don't even know if the queries I suggested will give you the results you want. If the queries generate errors, we'll try to fix them for you. If they don't give the correct results, you are going to have to describe what you want with business logic AND an example using the data you posted.

Thank you Pat Hartman for your assistance and I will try your code again this afternoon.

 

[access2010]

@access2010, I took the time to actually use your OWN column names, can you really not bother to type them in yourself? We're here to help you, not to do work for you that you are getting paid to do. I don't even know if the queries I suggested will give you the results you want. If the queries generate errors, we'll try to fix them for you. If they don't give the correct results, you are going to have to describe what you want with business logic AND an example using the data you posted.

Thank you Pat Hartmanand I will try your suggestions again this afternoon.
Crystal