Collapsing Duplicates to 1 Row??

[bmschaeffer]

I am new to access and having some trouble with a report. I believe the problem starts with my query. I have a database that has vendors in one column, posting period in another, and the amount in last column. I want to create a monthly trending report that will show the YTD expenses by vendor, but I need to create a report so it looks professional when given to a manager. I have a query in which the is a SUMIF column for each month. SUMIF posting period 1 for January, 2 for Feb and so on. But the report lists the same vendor multiple times because the expense is in different months. Is there anyway to collapse the duplicate vendors to 1 row?

Right now my report looks like this:


Vendor Jan Feb Mar Apr
XXX X
XXX X
XXX X

I want it to look like this:
Vendor Jan Feb Mar Apr
XXX X X X

[apr pillai]

You can use a Cross-Tab Query to solve the issue.

Define Vendor as Row Heading.
Format([Posting Period],"mmm") as Column Heading.
Expenses as Value, use SUM to create summary.

TRANSFORM Sum(Table1.Expenses) AS SumOfExpenses
SELECT Table1.Vendor
FROM Table1
GROUP BY Table1.Vendor
PIVOT Format([Posting Period],"mmm ");

[bmschaeffer]

I have used cross tabs before, but is there a way to turn the cross tab query into a neat report. I have to present this data to high level managers that do not want to see pivots tables or cross tab queries. I have a great report design for individual months, but when it comes to getting a YTD trend I am having issues.