Results 1 to 5 of 5
  1. #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. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,519
    Thanks
    3
    Thanked 50 Times in 50 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

  3. #3
    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

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

    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

  5. #5
    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
  •