Monthly Trend Query/Report

[bmschaeffer]

I have a table that contains a column called posting period. I want to run a query, that will find a report that shows a YTD trend by month by vendor. I know this can be done using a crosstab query, but I need to present it in a formal report. I have created a query that says this:



Jan: Sum(IIf([Posting Period]=1,[Amt in GRP],Null))

for every month, then I use that in a report to show the monthly trend. The problem I have now is it will list the same vendor 9 times because the expense amount is in a different column. It looks like this:

JAN FEB MAR
Vendor X 10
Vendor X 15
Vendor X 12

I need it to look like this:

JAN FEB MAR
Vendor X 10 15 12

Any help would be appreciated!!

[TheShabz]

"I know this can be done using a crosstab query, but I need to present it in a formal report."

Make the crosstab query and throw it into another query. Save the new query and base a report on it. You're done =]

[bmschaeffer]

Thanks for the suggestion!

When I try to use that query as the base for my report it freezes the report builder. Have you had problems in the past using this method?

[TheShabz]

It may be that since the query is being run on the fly, it is taking a while to populate the report. Instead of leaving the query that the crosstab is in as a SELECT query, change to a MAKE TABLE query instead, then bind the report to the table. If that is also freezing, then there is an issue with the database.