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

[Lkwdmntr]

I made a spreadsheet with all the data, but I am not great at pivot tables. I tried to attach the Excel document, but it is not allowed (I need permission to download it). The report consists of 12 sub-forms to make it look like one form. A copy of the report showing the columns and rows is already in the system and attached. You can see on page 5 when we started to add more actions in week 4, where the problem arises. Since the prior weeks didn't have the information, they are not showing. Only a real crosstab will fix this. I did pull all the data into a spreadsheet, but I am not good at pivot tables. Let me know what else might help here.

 

[DHookom]

One of your issues is the lack of normalization. You have activities and engagements in fields across rather than creating new related records. I am attempting to normalize tblChallenges with a union query. It will take a little work.

How do you calculate Planned, Engagement, and Achievement values?

 

[Lkwdmntr]

Hi there! Again, thank you for all your help with this. Normalization has been a challenge. This project changed weekly, and I had to accommodate my boss's crazy way of thinking. It has been quite the road and like I said in a previous communication, I think this is more of a hobby for her. She is very sweet and want to make her happy. So...I go forward with a smile and do my best to accommodate her hopes and wishes.

To answer your question. I calculate all the values when the client fills out there reporting forms. That data is attached to the tblChallenges table. When they check the box, the calculations is gathered with an IIF statement based on whether it is a Start Time or Activity Challenge. You can find the calculations in the qlkpStandardActionPointsByLoop_Wk_01, 02, 03...

That should answer your question. Thanks

 

[DHookom]

I’m traveling out of town this week without a laptop so won’t be able to reply. Did the PDF report pages correspond with the desired output based on the data in the file you provided? If not, could you provide files that align?

 

[Lkwdmntr]

Yes, enjoy your trip!

 

[Lkwdmntr]

Hi there, remember me. I hope you are enjoying your summer and enjoyed your trip. Chances are, you've been on a couple more since our last communication. I too, have had a couple of vacations and am now getting back on track with the project. I am still hoping that you can help us get through this last report. Your last question was about the data in the PDF, giving me the proper data. The problem lies with the report using so many different sub-forms that when actions are added later in the program, all the information is not displayed. If you open the PDF from a few comments ago, you will see that in week 4, we only see data and not the labels or empty weeks. It is just blank. You had a plan to normalize the data and get this report to display without all the different sub-reports. If you can let me know if you can help and what the next steps are, that would be great. Please let me know as soon as you can. We are anxious to get this up and running.

 

[DHookom]

I've been on a couple trips.
Can you build a query that has the User/client, Standard Action, Challenge/Day Type, Date, and the numbers displayed? I don't need or want the [Days by Week] whatever that means.

 

[Lkwdmntr]

So, here is the query that has all the information for week one. My problem is that I can't seem to make a query that will cover all the weeks in the program loop. Days of the week is the label for the numbers you see, which are the number of days that the client completed the action. They get a point for completing the action (achievement), a point for filling out the sheet (engagement), and the (planned) is the total points they can receive for each, it sets the benchmark for the week. I know it's a bit confusing, but it does make sense after a while.

SELECT tblWeeklyPlans.UserIDFK, tblActions.Action, tblWeeklyPlans.Loop, tblWeeklyPlans.WeekNumber, tblActions.Index, Sum(IIf([tblChallenges]![StartTime] Is Not Null And [tblChallenges]![StartTimeChallengeYes]=-1,1,0)) AS StartTimeAchievmentDays, Sum(IIf([tblChallenges]![StartTime] Is Not Null And [tblChallenges]![StartTimeChallengeYes] Or [tblChallenges]![StartTimeChallengeNo]=-1,1,0)) AS StarTimeEngagementDays, Sum(IIf([tblChallenges]![IsActivity]=-1 And [tblChallenges]![ActivityChallengeYes]=-1,1,0)) AS ActivityAchievementDays, Sum(IIf([tblChallenges]![IsActivity]=-1 And [tblChallenges]![ActivityChallengeYes] Or [tblChallenges]![ActivityChallengeNo]=-1,1,0)) AS ActivityEngagementDays, Sum(IIf([tblChallenges]![StartTime] Is Not Null,1,0)) AS PlannedStartTimeDays, Sum(IIf([tblChallenges]![IsActivity]=-1,1,0)) AS PlanedActivityDays
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
GROUP BY tblWeeklyPlans.UserIDFK, tblActions.Action, tblWeeklyPlans.Loop, tblWeeklyPlans.WeekNumber, tblActions.Index
HAVING (((tblWeeklyPlans.UserIDFK)=glngUserID()) AND ((tblWeeklyPlans.Loop)=glngLoop()) AND ((tblWeeklyPlans.WeekNumber)=1) AND ((tblActions.Index)<700))
ORDER BY tblWeeklyPlans.Loop, tblWeeklyPlans.WeekNumber, tblActions.Index;

I do wish I were more familiar with Access. I'm so busy with work and I don't have the proper time to study this better. I do appreciate your knowledge and patience in working with us.

Let me know what you need from me next. Thanks!