Need to print a crosstab report without complete data (1 Viewer)

[Lkwdmntr]

So I did a complete reinstall of 365, and as I said, it worked for the first time. I could see the query, but the next time I tried, it crashed. I looked in the Event Viewer, and this is what was found.

Faulting application name: MSACCESS.EXE, version: 16.0.18324.20168, time stamp: 0x677829a1
Faulting module name: ntdll.dll, version: 10.0.26100.2454, time stamp: 0x7cb6b6a8
Exception code: 0xc0000374
Fault offset: 0x00000000000881f5
Faulting process id: 0x2B88
Faulting application start time: 0x1DB64388FE7DA79
Faulting application path: C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE
Faulting module path: C:\WINDOWS\SYSTEM32\ntdll.dll
Report Id: 317bc520-dab1-4550-b497-07e5b754f14c
Faulting package full name:
Faulting package-relative application ID:

 

[DHookom]

I’m not sure if this helps but I found this page about ntDLL.dll.

 

[Gasman]

So I did a complete reinstall of 365, and as I said, it worked for the first time. I could see the query, but the next time I tried, it crashed. I looked in the Event Viewer, and this is what was found.

Faulting application name: MSACCESS.EXE, version: 16.0.18324.20168, time stamp: 0x677829a1
Faulting module name: ntdll.dll, version: 10.0.26100.2454, time stamp: 0x7cb6b6a8
Exception code: 0xc0000374
Fault offset: 0x00000000000881f5
Faulting process id: 0x2B88
Faulting application start time: 0x1DB64388FE7DA79
Faulting application path: C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE
Faulting module path: C:\WINDOWS\SYSTEM32\ntdll.dll
Report Id: 317bc520-dab1-4550-b497-07e5b754f14c
Faulting package full name:
Faulting package-relative application ID:

0xc0000374 - Google Search

 

[Lkwdmntr]

Thanks for that information. I did the scans and ran a malware scan; it got a few things, but nothing major. It's still happening every time I go to access that query. I'm perplexed. My next option is to complete a Windows installation, reinstall 365, and see what happens. It is troubling that it only happens with that query "qrptSAsByWeek'. I have no trouble accessing any other queries. I have never used the "Transform' statement and am wondering if that's what is causing the issue. I will try the Windows installation; it wouldn't hurt to get a fresh start. I usually do one every three years, and I think it's been five.

 

[DHookom]

Did the example I attached in post #9 work as expected?

The only other thing I see in the application is the data type of parameters in crosstab queries (or their sources) typically need to be specified. This isn't typically the case when the column headings are specified. It would hurt to modify qfrmWeeklyReports like:

 

[Lkwdmntr]

I am trying all the suggestions with no success. I am resetting my computer. It doesn't make sense that it works on my work computer and not my personal one. I'll let you know.

So, if I do get this to work, my next question is to ask why there is a W0. It doesn't seem very clear. I tried to eliminate it and lost the week 12 data on the report.

There are also duplicate Actions displayed on the report. Attached is the report the way it should look, except for the weeks not working on this one. It is also not sorting by index, and won't let me sort it because the index isn't on the report.

 

[DHookom]

There is a week 0 because the expression creating the column heading in the crosstab query calculates the number of weeks between the start date and the data date. You can easily add 1 so your start week becomes Week 1 but nobody sees the Week 0.
Expr2: "W" & DateDiff("w",DMin("StartDate","qfrmWeeklyReports"),[ChallengeDate])+1

You can use the Format property of a text box to display an X for all positive values.

You should also be able to add the index field so you have something to sort by in the report design.

 

[Lkwdmntr]

Well, this would all be great if I could get in and access the query. I'll have to wait till I'm back on my work computer. I did a fresh install of Windows, and I still can't open the query without it crashing.

Still have the issue of duplicated actions.

 

[DHookom]

I had some issues simply running the report until I deleted a bunch of the text boxes in the header (using tempvars etc). The issue I had wasn't related the beef of the report, just the non-record data.

 

[Lkwdmntr]

I am so frustrated. I thought it was my computer, so I went out and bought a new one. Nice one, too. Alienware with an i9 processor. I went to run the query, and it did the same thing. Blue circle for a few seconds, and then the application closes. It is only with that query. I went and compared all the settings in Access on my work computer, and they are all the same.

I am at a complete loss. I need to be able to run this on any computer.

 

[Lkwdmntr]

I am still trying to clean up the report on my work computer. I was able to get the X's in, sort it by the index, and change the source of the text boxes in the header, with the exception of the Name. I am still stumped on why it is pulling double of a few of the actions.

 

[DHookom]

Are you using the same data on all computers? I would need to review your data to determine why there are duplicates.

I think the duplicates is because the crosstab doesn't filter to a single user.

 

[Lkwdmntr]

Great, got it! I tried to figure out why the one action is showing in bold. Any idea?

 

[Lkwdmntr]

I do have one more report that I need to fix. This one is set up strangely. Probably to know surprise! LOL. As with this last report I am having difficulty with the crosstab function. I can only include a certain amount of fields when I create the query. Using the same program you already have. Still using John D. Accelerated as the client, navigate to the reports menu and pull up the Weekly Progress by Standard Action report. You will notice I have subreports designed for all the weeks, with week one containing all the labels. It works ok, but if an action gets added past week one, the labels won't show.

I know this is not a good design, and I have had to do a few reports like this. I would love to learn how to pull this data into one complete report.

If this could be fixed, perhaps some of the other reports using this design can also be fixed.

Take a look and see what you think. Thank you so much for all your help. You have been great!

 

[DHookom]

Congrats on getting the first question to work. I’m not sure why the bold but it would be interesting to know if this was bold in a printed or to PDF report. A possible cause is code or conditional formatting.

I’m away from home/PC for a couple days so I can’t look at your new question.

 

[Pat Hartman]

If you have several distinct sets of criteria but all the columns are the same, you can create several crosstab queries and union them. Just make sure to include a dummy column in your crosstab queries so you can keep the "sets" separate and use the dummy column as your break criteria in the report.

 

[Lkwdmntr]

Congrats on getting the first question to work. I’m not sure why the bold but it would be interesting to know if this was bold in a printed or to PDF report. A possible cause is code or conditional formatting.

I’m away from home/PC for a couple days so I can’t look at your new question.

Thanks, DHookom; I did find the conditional formatting and fixed the bolding issue.

Enjoy your time off, and if you can look at the subsequent report at your leisure, that would be great.!