Reports with Columns (97)

Topic

New Reply

1. I have 2 tables that are related by primary key.
A) tblPeople: Fields are – MemberID(autonumber and primary key) LastName, FirstName, Class, Level.
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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote