Results 1 to 14 of 14
  1. #1
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39

    Ideas for Summary Table in [Dashboard] Form

    Hi,

    I have a [Dashboard] form which my access database opens to. I've added some summary text boxes (with calculations) into it for some quick glass information. For example, a text box that shows Total Sales for the month. As time has progressed, I've found that I want more information on it and it's become slightly cluttered with text boxes here and there. I'm looking for ideas or a suggestion on how others have handled this.

    I thought an excel type table would work perfectly in there, is there a way to added some similar to that? Where rows could be Sales, COGS, etc and columns would be Jan, Feb, Mar.



    Any ideas would be great!

    Thank you.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A CROSSTAB query could pivot data to summarize under monthly headers. However, building a form or report to run perpetually based on CROSSTAB is not easy.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39
    Thank you again June7 for your help,

    The quick look I gave Crosstab seems like it could do the job of what I'm looking for.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I've never tried, but could maybe set the SourceObject property of subform container control to the CROSSTAB query object. Or maybe a listbox RowSource can draw from CROSSTAB query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39
    Setting the sourceobject worked great with the crosstab query. Its displaying exactly the way imagined. On the crosstab query I have a row set to sum. I cant seem to add another row below it As sum also.

    Of course, we lost the morning so I apologize for any typos....

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Click on subform, then click the Totals button from ribbon. Does that open the Totals row?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39
    I have column totals in both the crosstab query and the subform displaying that query. Its looking geeat!

    The crosstab is setup with months cross the top (columns) and one row on the left (cogs). I've been playing with it and cant seem to get than one row on the left side.
    For example:
    Months cross the top.
    Cogs (row)
    Supplies (row)
    Expense (row)
    Etc

    Now that I'm typing it out. Will I be able to sum rows? If not maybe I should reverse it...

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What do you mean by 'sum rows'. If you build the CROSSTAB with the wizard it will create a Totals column automatically.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39
    Hi June7,

    After I typed that I did realize that I do have row totals.

    This is the point I'm at. I have a CrossTab Query with a row of "COGS" set up perfectly. It has Months as Columns with a Totals Column at the end. What I can not figure out is to add another row underneath "COGS" to the query, which would represent some different for example "Direct Business Expenses". I've gone as far as to make two CrossTab Queries and then a Union Query to combine them then add the union query to the form. However, I was hoping to have six or seven rows, which would require six or seven CrossTab Queries. Also, this seem to be a round about way of doing this getting more then one row.

    So the question is(I think),
    Is there a way to add multiple rows to one Crosstab query?

    I hope you can follow this........ Thanks for you help!

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't understand the data. Why does the CROSSTAB have only one row? Provide sample raw data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39
    Sorry for the terrible explanations......

    I'm doing just the sum of the row under each month. Not the values broken down individually. I seem to have done this just fine. Just trying to add another row that would represent a different "column" from my source.

    I'm using just the sums because I'm just trying to create a summary type table.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sorry, still don't understand data structure nor what you mean by 'add another row that would represent a different column'.

    Provide example data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39
    Hi June,

    I tried narrowing down data but I just have so much in my sheet, I'm not sure where to start.

    My CrossTab Query Design View Looks like this:

    1.
    Field: SaleDate: Format([Sale Date],"mmm")
    Table: Sales2015
    Total: Group By
    Crosstab: Column Heading

    2.
    Field: COGS: "Cost Of Good Sold"
    Table:
    Total: Group By
    Crosstab: Row Heading

    3.
    Field: COGS
    Table: Sales2015
    Total: Sum
    Crosstab: Value

    This looks perfect. It sums COGS in one cell under the month. However, I would like to add a couple more "Crosstab: Value?"/Rows underneath COGS. But, I can't seem to make it work.

    What I did make work was creating a couple different Crosstab Queries and then Union Query to get them all in one query. Just seemed round-about-ish.

    I really have no idea if this will explain it better...... Probably not..... I'll try and figure out a way to narrow down my database so you can take a look.

  14. #14
    Dormie is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    39
    Hi June7,

    I don't want to bring this back from the depths but wanted to thank you for your help. After trying a few different things, I have a nice pivot table for a decent summary sheet on my dashboard. Looks works awesome.

    Thank you!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-23-2014, 06:03 PM
  2. Need ideas on how to organise table..
    By Calvinle in forum Access
    Replies: 7
    Last Post: 09-18-2014, 12:21 AM
  3. CrossTab to Summary Table
    By Dennis Willis in forum Queries
    Replies: 3
    Last Post: 06-09-2014, 01:06 PM
  4. Replies: 3
    Last Post: 05-24-2013, 04:39 PM
  5. Replies: 2
    Last Post: 05-23-2013, 08:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums