Consulting

Results 1 to 5 of 5

Thread: Query Role Up Help

  1. #1
    VBAX Regular
    Joined
    Sep 2020
    Posts
    62
    Location

    Query Role Up Help

    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.

  2. #2
    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).

  3. #3
    VBAX Regular
    Joined
    Sep 2020
    Posts
    62
    Location
    Quote Originally Posted by arnelgp View Post
    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!!!
    Attached Files Attached Files

  4. #4
    see if this is what you need. note i added another table for the crosstab use.
    see Query1, Query2 or Query4.
    Attached Files Attached Files
    Last edited by arnelgp; 04-12-2022 at 08:05 PM.

  5. #5
    VBAX Regular
    Joined
    Sep 2020
    Posts
    62
    Location
    Quote Originally Posted by arnelgp View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •