Grouping Assistance

[access2010]

Could we please have assistance if having two grouping in our Access 2003 report?

Group A: = For each equity as a one line total.

Group B: = By Portfolio Code, a one line total for each equity.

Thank you
Crystal

 

[Ranman256]

Do exactly that in report grouping. set the sort order.
Is this not working?

 

[June7]

Define "equity" - the Stock_Name?

For Group B why not add a Portfolio group above Stock_Name? Can set Detail section as not visible.

If you still want a Group A where stocks are listed alphabetically without regard to portfolio, that would be a separate report which could be a subreport on this main report in report header or footer.

 

[XPS35]

A

SELECT Stock_Name, Sum(TransactionQuantity) AS SumOfTransactionQuantity
FROM Investments_Purchases_SalesT
GROUP BY Stock_Name

B

SELECT PortfolioCode, Stock_Name, Sum(TransactionQuantity) AS SumOfTransactionQuantity
FROM Investments_Purchases_SalesT
GROUP BY PortfolioCode, Stock_Name

You will need a report to display the totals per PortfolioCode.

 

[access2010]

Do exactly that in report grouping. set the sort order.
Is this not working?

Thank you, Ranman256 for your suggestion.

Below is what we have tried without any success, in our Access 2003 report, using Sorting and Grouping, without any success;
A = Group Header / Yes / Keep Together / Whole Group
B = Group Header / Yes / Keep Together / Whole Group
C = Group on / each of the choices
D = Keep Together / each of the choices

But, we still have not been able to have a one-line total for each symbol.

Could you please let us know what we have done incorrectly and your assistance will be appreciated.
Crystal

 

[access2010]

Define "equity" - the Stock_Name?

For Group B why not add a Portfolio group above Stock_Name? Can set Detail section as not visible.

If you still want a Group A where stocks are listed alphabetically without regard to portfolio, that would be a separate report which could be a subreport on this main report in report header or footer.

Thank you, June 7 for your question. “Yes” Equity is the Stock_Name”.

We tried to add a Portfolio Group as you suggested, but for some reason we could not get this function to work.

We also tried to add the sub report as you had suggested and could not get this function to work also.

Would you have a suggestion as to what we are doing wrong in our Access 2003 report?

Thank you,
Crystak

 

[access2010]

A

SELECT Stock_Name, Sum(TransactionQuantity) AS SumOfTransactionQuantity
FROM Investments_Purchases_SalesT
GROUP BY Stock_Name

B

SELECT PortfolioCode, Stock_Name, Sum(TransactionQuantity) AS SumOfTransactionQuantity
FROM Investments_Purchases_SalesT
GROUP BY PortfolioCode, Stock_Name

You will need a report to display the totals per PortfolioCode.

Thank you XPS35 for your suggestion.

Should we have placed your code on a form to open the Access 2003 report, as this is what we tried without success?

Thank you.
Crystal

 

[June7]

I had no problem adding a Portfolio group section in report design. Why could you not accomplish? Exactly what did you attempt? My suggestion is unchanged.

 

[XPS35]

Thank you XPS35 for your suggestion.

Should we have placed your code on a form to open the Access 2003 report, as this is what we tried without success?

Thank you.
Crystal

This is a the SQL for a query. You can use this SQL as the recordsource of the report. Or make a query with this SQL and use the qeury as recordsource for the report.

 

[access2010]

Thank you XPS35 for your assistance.

We now have the results that we wanted.

Nicole