• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

How format report from query results

If you look at the sample screen shot I attached...

I am trying to figure out how to develop a report using the data.  But the report has to be formatted like:  (the field listing month = the name of the month)

                                                Jan     Feb     Mar     Apr      May     etc.-------

CountofCountOfID
SumOfListingPending
SumOfListingSold
etc. (These will be labels
on the report)

In other words, the months have to be displayed horizontally.  How can I do this?

--Steve
queryscreenshot.jpg
0
SteveL13
Asked:
SteveL13
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need a CrossTab report. Essentially you first create a Crosstab query, and then create a report based on that query.

See this MSFT article for more information: http://office.microsoft.com/en-us/access-help/create-a-crosstab-report-HP005187407.aspx

Be sure to make your query as complete as possible and be careful with column naming, since that can cause quite a bit of grief later on. See this MSFT blog posting for more info:

http://blogs.office.com/b/microsoft-access/archive/2012/05/23/using-crosstab-queries-in-reports.aspx
0
 
Jeffrey CoachmanMIS LiasonCommented:
Another workaround would be to simply insert the Crosstab query into a report as a sub report.

I am no expert on crosstab queries, but I am not sure getting multiple summaries on the bottom of he query/report will be easy.

Also note that your source data must be somehow filtered for a year, ...else each month will contain the data for all years (For example; the January column will contain data from 2012, 2013, 2014, ...etc)
0
 
Jeffrey CoachmanMIS LiasonCommented:
I am sorry, I did not realize that one of Scott's posts included a link for inserting the crosstab query as a report...
:-(

But here is one more if you are feeling bold...

You could also build a report and include aggregate functions
It may look tedious, but once you get the first month done, all you need to do is copy all the summary textboxes for that month and increment the month value in the formula by 1
This may be an option if you want more control over how you format the report...
(I did this for a co-worker because they wanted to do things like conditional formatting ...etc, with the output (They also wanted sums of the summaries, so it really got complex...

The bottom line is that since a crosstab query "dynamically" creates the columns each time it is run, (based on the current data), you cannot "easily" create a Crosstab Report.
In a report, the columns are hardcoded.

What Scott posted are the standard ways to create true "crosstab" Reports
My suggestion here is another to get the same output, with a "simulated" crosstab. (it may run a bit slower than Scott's suggestions

..take from his what you like...
...and enjoy the weekend

JeffCoachman

Sample attached
Access-EEQ-28340433-Aggregate-Si.mdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now