Query Role Up Help

[twmills]

Hello,

I have a table in Access database that lists about 700 line items. The way it's setup is that each Account Number will have its own row with its corresponding Account Value, Shares Owned and Share Class next to it in the same row. The Share Class is either classified as Class D, Class F or Class I.

I'm trying to create a query that roles up each Share Class into one line item, then lists the sum total of the Account Values and Shares Owned for that Share Class. This is something that could be easily done in Excel, but I'm not familiar with how to setup a query in Access that could do this. Since there's just 3 share classes, the end result should be only 3 rows (one row for each Share Class). Then create some sort of access version of a SUMIF to total up the value and shares. Account numbers won't be important in the query result.

Then, to possibly take it one step further, I need an additional column that calculates the Percentage of Shares owned for each Share Class. So if Class D has total 3,000 total shares - and the grand total of all 3 share classes is 10,000 - then Class D will be 30%.

Let me know if more info is needed.

Thanks so much in advance.

[arnelgp]

better upload a sample db to see if the required Joining fields are there.
on the way i see it is you will need a Crosstab query for you shares (with Share Class as the Column headers).

[twmills]

better upload a sample db to see if the required Joining fields are there.
on the way i see it is you will need a Crosstab query for you shares (with Share Class as the Column headers).

No problem...Attached is a zip folder with an example of the table I need that query performed on. You may need to hold down SHIFT to view the table when opening the database.

First column has the Share Class. I'll need a query that shows how many total shares and total account value there is for each Share Class. then calc the overal percentage of each Share Class, in relation to the whole file.

Thank you!!!

[arnelgp]

see if this is what you need. note i added another table for the crosstab use.
see Query1, Query2 or Query4.

[twmills]

see if this is what you need. note i added another table for the crosstab use.
see Query1, Query2 or Query4.

Yes, Query2 was exactly what I was looking for.

Thanks so much!