Crosstab query report not showing all data

Question

I have been trying to create a report that would give me information in a "Pivot table" kind of way. I was getting pretty far with the pivot table when I realized it looked horrible in print...

To give a bit of background: I have to report results for various test on a monthly basis for the prior 6 months, I need to find a way to present the data that would not end up on 19 pages (we have about 40 different tests, 6 months each, it's horrible).
I have re-designed the tables driving the reports, it used to be that each record was for a test and there was a column for each month, #of files, root cause etc. There were about 60 columns for each record; most were not relevant to the record. Now it's 1 record per test per month which is 7 columns. The old way of making the report was to change the record source of all the columns and boxes in the report; this is what it looked like:

I found that the Crosstab Query sort of gives me the feel I want to get and I tried to create reports in so many ways I just can't seem to figure it out....
Here is my issue. When I create a crosstab query I get all the data I need, (can't find how to get the last 6 months only, what happens for the change of year or how to have it change on a monthly basis but that's another question entirely).

Crosstab query result in Query view

However when I select the query and click "Report" to let access create the report, it gives me this:

I have no idea what I'm doing wrong but I'm literally letting access create this and that's what I get!

I looked at so many youtube videos, so many google search and forums.... I'm getting really frustrated.

Please help!

Answer 1

Hi Jessica,

I am using Access 2010 and I just tried creating a Crosstab Query using the Wizard and then used the Wizard to create a report based on it, and everything looked fine.

I'm not sure why your result is different.

Answer 2

Sounds weird.  Do a Compact/Repair and try again.  Over the years I have seen Access do all kinds of inexplicable things.  Almost without exception, a good old-fashioned CR will fix it!!

---

MY BOOK

Answer 3

Crosstab queries have their place, and I use them where necessary, but by and large I avoid them where an alternative solution is available.  Often you can produce a report which, like a crosstab query, has data as 'column headings', by using subreports with 'across-then-down' column layout.  The parent report supplies the 'row headings'.

You'll find examples in Payments.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

These little demo files illustrate the methodology by means of reports of total payments per day per customer, with the dates for each week as seven 'column headings'.  The latter are returned by one subreport, while the amounts are returned by another subreport.  The parent report's query returns the customer, the sum of the customer's payments per week and the week-starting date.  The 'column headings' subreport is linked to the parent report on the week-starting date, while the payment amounts subreport is linked to the parent report on the customer and week-starting date columns.  The parent report's and both subreports' result sets are restricted by referencing parameters in the dialogue from which the report is opened.

While my files are for daily events, with one week's data reported in seven columns across the page, the same methodology would easily allow for monthly events to be reported across the page.  In either case an auxiliary calendar table is needed to supply the dates for which no data are recorded.  See Calendar.zip in the same OneDrive folder for a means of building calendar tables of various types.

The real benefit of this approach is that, as the 'column headings' are entirely data driven, unlike a crosstab query, there is no need to manipulate them to reflect the column headings of a query's result table where these are not a fixed set.

---

Ken Sheridan, Stafford, England

Answer 4

Hi JessicaL01,

I agree with other community members regarding compact and repair the Access Database.

so that if something get corrupted then it can be corrected by compact and repair.

I also suggest you to create a new report and check whether the same issue is exist or not.

if the issue is exist then try to create a new database and try to reproduce the issue with dummy data in it.

if that work correctly then you can try to move your all database objects from old database to new database.

if you can able to share your database with dummy data in it then we can try to make a test with it and try to reproduce the issue on our side.

we will check whether it is corrupted or any mistake was made when creating a Report.

so you can try to do testing mentioned above and let us know about the results.

We will try to provide you further suggestions to solve the issue.

Regards

Deepak 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi JessicaL01,

is your issue solved?

I find that you did not follow up this thread after posting the issue.

if your issue is solved then I suggest you to post your solution and mark it as an answer.

if your issue is still exist then try to refer the solution given by the me.

if then also you have any further questions then let me know about it.

I will try to provide further suggestions to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi all,

Thank you for your help, I was away for the holidays and was unable to thank you/follow up as you replied.

I did find an alternate way of doing the report, it was very similar to what Ken suggested with a Sub Report and used the column option in the print screen. it's a bit slow to pull but it looks good and I think this will be an acceptable outcome.

Thanks again everyone. I will take a look at the material Ken provided only at work I cannot open links like these.

Answer 7

Ken, I really like your solution of this. For me the question is how you get the report formatting - rptSubDates and rptSubPayments according what you need (I mean to create "virtual pivot" formatting)?

Answer 8

Ken, I really like your solution of this. For me the question is how you get the report formatting - rptSubDates and rptSubPayments according what you need (I mean to create "virtual pivot" formatting)?

1.  The parent report's RowSource property will be a query which returns district test values.  These provide the 'row headings'

2.  The first subreport will return the 6 Year/Month values you wish to report on, using an auxiliary calendar table (see below).  This provides the 'column headings'.

3.  The second subreport will return the results per test over the 6 month period in question.  This is linked to the parent report on the test columns, so for each row in the final report you get only the results from the test in question.  If each test always has results for every month, then this subreport's query will be based on a TestResults or similar table only.  If this is not the case, however, then you need to fill in the missing months by means of an auxiliary calendar table.  In my case this has one row for every day, but in your case it will have one row for every year/month over a period of ten or so years into the future.  The values must be consistent with the way in which the year and month are stored in the TestResults  table, so that the tables can be joined in the query, using a LEFT OUTER JOIN so that all year/months are returned from the calendar table regardless of whether there is a match in TestResults.

4.  Both subreports' RecordSource queries are restricted by referencing parameters in a form.  In my case they are the 'week beginning' dates; in your case they'll be the year/month values

---

Ken Sheridan, Stafford, England