Reports with Columns (97)

[socrates]

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

[WendellB]

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.

[socrates]

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

[socrates]

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

[charlotte]

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.