Need to print a crosstab report without complete data

[Lkwdmntr]

The important question is whether or not [B]kwdmntr[/B] was able to create the desired report. I would be happy to take their database and apply the simple technique.

Hello, I have not had much time to put into this project and was wondering if there was a way we could Zoom or Teams meet to help resolve this issue I am having. I don't want anyone to do the work for me; I want to understand what is happening and learn better with a hands-on approach. If you have some time to meet, let's set something up. Thanks a bunch!

 

[Lkwdmntr]

If I were to seek paid support, how would I do that? I want to take the faster/best route as you recommended.

 

[DHookom]

I have attempted to share the concepts with working examples and even spent a fair amount of time attempting to reverse engineer your database. I am worried any Zoom session would be going down a rabbit hole that I prefer not to.

I am open to answering specific questions here where the thread is shared publicly.

 

[Lkwdmntr]

I do thank you for taking the time to help. It gets a little frustrating, especially since I don't have a great deal of time to put toward this. I am doing this for a friend and just want to get this working for them.

That being said I have take the query you have made and put it into my database, I acquainted the fields to the W1, W2... When I go to run it I get an "Unknown" error.

I followed it through the code and it stops when it goes to pull the report. I checked and the name is in the properly.

 

[DHookom]

The least you could do is to mention the object names so people wouldn't have to dig through dozens of forms and reports.

This might take a while to dig...

 

[DHookom]

I'm not sure of the specification for your report. Also, there isn't significant data to validate results.

 

[DHookom]

I think I have some of this working. I don't know why you used such a complex expression just to get the start date of the report. I changed the report's record source query as well as updating some control sources. The shaded text boxes need updating based on a continuance of the logic from the controls to the left.

 

[Lkwdmntr]

One of the issues was that the report was created after I had already had 12 weeks of data. It wasn't until I cleared out all the test data and started testing with new data that I found the original error. The report wouldn't run unless it had all the data.

I did the DLookups for the dates because if the client needed to take a week off and start back up, I would have the actual date. I can use your way for now to get this running. I can go back in and tweak it later. Maybe do an If statement that will grab the actual date if it's there and put it in the way you did it if there is no data for that week yet.

I can go in and change the data, but every time I try to look at the SQL statement, Access crashes. Any idea why that is happening?

When I go to run the report as you have it, it still gives me the "Unkown" error.

I am sorry I didn't include better direction. I thought you knew where to go since you had already been in the program.

 

[DHookom]

I don't have any issues with the file I zipped and attached. The SQL of the report's record source query is

TRANSFORM Sum(Abs([IsActivity])) AS Expr1
SELECT tblActions.Action, tblWeeklyPlans.UserIDFK, tblWeeklyPlans.Loop, DateValue(DMin("StartDate","qfrmWeeklyReports")) AS Start
FROM tblWeeklyPlans INNER JOIN (tblActions INNER JOIN (tblActionsPerWeeklyPlan
INNER JOIN tblChallenges ON tblActionsPerWeeklyPlan.ActionsPerWeeklyPlanID = tblChallenges.ActionsPerWeeklyPlanIDFK)
ON tblActions.ActionID = tblActionsPerWeeklyPlan.ActionIDFK) ON tblWeeklyPlans.WeeklyPlanID = tblActionsPerWeeklyPlan.WeeklyPlanIDFK
WHERE (((tblActions.Index)<700))
GROUP BY tblActions.Action, tblWeeklyPlans.UserIDFK, tblWeeklyPlans.Loop, DateValue(DMin("StartDate","qfrmWeeklyReports"))
PIVOT "W" & DateDiff("w",DMin("StartDate","qfrmWeeklyReports"),[ChallengeDate]) In ("W0","W1","W2","W3","W4","W5","W6","W7","W8","W9","W10","W11","W12");

Does your query design look like this:

 

[Lkwdmntr]

Something is messed up with my Access application. I pulled up this program on my work computer, and it's working. It still needs some tweaks, but it crashes when I open the query on my personal computer. I uninstalled and reinstalled MS 365 and got into the query once, but after that, it kept crashing.

Any thoughts on this? I'm considering refreshing my computer, but I don't want to do that unless I must.

 

[DHookom]

You could attempt a reinstall of Office/Access.

 

[Gasman]

Something is messed up with my Access application. I pulled up this program on my work computer, and it's working. It still needs some tweaks, but it crashes when I open the query on my personal computer. I uninstalled and reinstalled MS 365 and got into the query once, but after that, it kept crashing.

Any thoughts on this? I'm considering refreshing my computer, but I don't want to do that unless I must.

Look in the Event viewer to see if the crash gives any clues?

 

[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.