Access report and grouping question

Posted on 2011-10-28
Last Modified: 2012-05-12
Is it possible in Access to have a report that can display data in this style format:

NY     Buffalo       Sabers
          New York  Rangers
PA    Pittsburgh    Penguins
         Philadelphia Flyers
NC    Raliegh         Hurricanes
CA    San Jose      Sharks
         Anaheim       Ducks
         Los Angeles Kings

With the blank spaces meaning repeating state or city values

I've tried the group and sorting features for the various columns but I haven't been able to get the format as you just saw.  Any ideas?
Question by:shuittny
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Assuming you are starting with a query or a table that has columns State, City, and Team, then use your report wizard to create a report based on that query/table, and in the grouping options group first on State, and then on City, leaving Team in the Detail section.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Also these "Layouts" vary slightly depending on the version of Access.

    So if you state the version you are using, we can tell you what option to select.

    Author Comment

    True that kinda works except that process will look like this

               New York          

    and State, City and Team wouldn't be on the same line like this

    NY Buffalo         Sabers
          New York    Rangers

    Author Comment

    @boag2000: Access 2003
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    I only have 2007 and 2010 installed here at work...

    I'll check at home tonight if Patrick does not post back
    LVL 74

    Accepted Solution

    The closest output to what you are asking for can be obtained by selecting the "Block" layout.
    Unfortunately you have to make the Borderstyle of all of the controls in the detail section: "Transparent"

    Outline1 and Outline two are similar, but the Grouping field will be in the Heading, ...not aligned on the same row as the other fields as you sample demonstrates.
    The good thing here is that it looks "Similar" to what you are asking for, and no design view modifications are needed.

    To get the *exact* output you are requesting you would:
    Use the wizard to create a simple report with all the fields you want, in the order you need them
    (State, City, TeamName)
    Sort by State, then by City
    *Do not* select a grouping Option.
    Then select the "Tabular" layout option.
    Then open the report in design view and set the "Hide Duplicates" property of the State and City textboxes to: Yes

    This will give you the *Exact* output you are requesting, but it requires the most work in design view.

    All this being said, I would do as matthewspatrick suggested and select Outline2 as the layout.
    So here I am merely providing some supporting notes to matthewspatrick's original post, if you find this info useful, you should accept his post.



    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now