Results 1 to 5 of 5
  • Thread Tools
  1. Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports with Columns (97)

    1. I have 2 tables that are related by primary key.
    A) tblPeople: Fields are - MemberID(autonumber and primary key) LastName, FirstName, Class, Level.
    [img]/forums/images/smilies/cool.gif[/img] tblAnnualAppealHistory: Fields are - MemberID(foreign key points to primary key), Year, AmountContributed. Thus, tblAnnualAppealHistory has 4 records for each record in the tblPeople table.

    What I want is:
    I want to print the Year field value as Coulumn Headings - 1997 1998 1999 2000 across the top.
    I want the values in the table that correspond to the year to be printed under the appropriate year.
    EXAMPLE: 1997 1998 1999 2000
    --------------- 1-------3------ 5-------7
    ---------------- 2-------4-------6-------8
    -----------------11-----20-----55------9

    I want the names printed only once: EXAMPLE
    -----------------1997 ------1998--------1999-------2000
    Joe Doe-------1------------3---------------5 ---------7
    Jane Doe------2-----------4---------------6----------8
    Bob xxx---------11---------33-------------55---------66

    As it now stands:
    I get the year across the top of the page.
    I get the correct amount printed in the correct column.
    But I get the name repeatedly printed beside the amount - therefore the name appears 4 times- once in each column.

    I have tried different grouping sorting methods but can't get it. Any ideas?

    Soc

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,468
    Thanks
    2
    Thanked 39 Times in 39 Posts

    Re: Reports with Columns (97)

    This is a classic cross-tab query, which you can turn into a report. Try using the cross-tab query wizard, and I think it will do the trick for you. You may want to combine the name into a single field before you choose it as a row heading.
    Wendell

  4. Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with Columns (97)

    Thank you so much. This site is the best. I have learned so much and look forward to more learning.

    It worked like a charm.

    Soc

  5. Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports with Columns (97)

    I'll try that.

    I also found out that a cross tab query does not deal with the following:

    criteria row in query grid - [Enter the name of Board Member]-------for the reason that it does not recognize the [ brackets].

    Have you seen that before?

    Soc

  6. Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reports with Columns (97)

    If you want to use parameters in a cross tab query, you have to add the parameters, not just the criteria, and it may be necessary to specify column headers.
    Charlotte

Posting Permissions

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