Results 1 to 4 of 4
  1. #1
    Vaslo is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    23

    Grouping of groups - group business cost centers into business segments

    Hello,

    I am in the midst of converting a dynamic summary from Excel to Access - a rather large report that looking nice in Excel but is rough on resources. Basically for this part, what I'd like to do is group a bunch of cost centers into business segments, and there will be some overlap because I will sometimes want to group all in one, or a few together for a specific director, or I'll want them singularly etc. They want use the numbers to do this, they will use the group name like M or B or All.

    Currently, the file was setup to use a table in excel and do a true/false lookup to ensure the right cost centers were captured to roll up. I posted a sample below, but effectively in the excel file when you pick All North America from a list, it sums all those Cost Centers that are true through a Sumifs. Sometimes you want F, sometimes you want GP, their boss will want F&GP together. What is the right way to approach this grouping in Access? Do I need this type of grid or is there a conceptually better way to lay this out? Thanks!

    PC ALL North America NA - No Adj. M B 2P BE F&GP F GP GP2 SC C TM BC Adjustments M/J
    3000 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE
    3071 TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE
    3630 TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE
    3359 TRUE TRUE TRUE FALSE FALSE FALSE TRUE FALSE TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE
    3706 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE
    3729 TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
    3730 TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
    3780 TRUE TRUE TRUE FALSE FALSE FALSE TRUE FALSE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE
    3787 TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE
    3788 TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE TRUE FALSE FALSE
    3789 TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE


  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,825
    Forget what you know about Excel when designing an Access database.

    You need understanding of relational database concepts and Access functionality. Maybe this will help http://www.rogersaccesslibrary.com/

    Identify data entities and relationships, design table structure, then work on how to manipulate the data with queries, forms, reports, code.
    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
    Vaslo is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    23
    I have a good understand on putting this database into 1NF, and it has referential integrity through 10-11 linked tables. I also understand how to build queries by grouping, etc. to get some of the data I need. I guess I am looking for hints here on how to think about this. How do you group groups? I'm so deep into ensuring that things have a primary key that this one stumps me - that said, would I just do this in the reporting piece?

    Thanks.

  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,825
    Possibly. A report with multiple grouping levels might provide what you want.

    Filtering records is a different issue.
    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.

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

Similar Threads

  1. Business Weekday?
    By HMEpartsmanager in forum Queries
    Replies: 2
    Last Post: 10-01-2012, 01:28 PM
  2. CRITERIA only looks at the LAST 20 BUSINESS DAYS
    By taimysho0 in forum Queries
    Replies: 3
    Last Post: 12-06-2011, 06:27 PM
  3. First week or business day of the month
    By Dutch1956 in forum Programming
    Replies: 12
    Last Post: 09-18-2011, 08:39 AM
  4. Replies: 1
    Last Post: 07-26-2010, 06:22 AM
  5. Loading data for our business
    By fsmikwen in forum Programming
    Replies: 2
    Last Post: 04-16-2010, 09:13 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