Need to print a crosstab report without complete data

[DHookom]

I just left for a week vacation with only phone and iPad. I’ll have to look at this in a week or so. Is there a good reason why there activity yes and activity no fields rather than a single field?

 

[Lkwdmntr]

Yes, because of the points. Not only are they being calculated on the form, but they are also being stored for tracking. They get points for checking the yes and no fields and then another point if its a yes.

I just left for a week vacation with only phone and iPad. I’ll have to look at this in a week or so. Is there a good reason why there activity yes and activity no fields rather than a single field

 

[DHookom]

I'm at a loss trying to understand your application.

 

[Lkwdmntr]

What can I do to help you understand this better? I, too, have struggled with this. The person who came up with this idea approached me a long time ago. I genuinely think this is more of a hobby for her. I have been working slowly on this application for years and she keeps finding things to add or change. She is a wonderful person, and I love working with her, but I don't think this will ever really go that far. I am just trying to give her life some purpose with these hopes of hers.

You can let me know what I can do to help you understand this program so I can finish this report for her. I want a complete program for her and hopefully pass this on to someone else. I have a full-time job that I need to focus on, and I can't do a whole lot for her going forward. I'm just trying to finish what we started.

I hope you can understand my dilemma.

 

[Lkwdmntr]

The points are all about accountability. After reading the one comment I sent you, I can understand the confusion. I had to understand this a long time ago, but I do remember how confusing it was when we first started. When the client is filling out their reporting form, whether they complete the task or not (marking yes or no) makes them accountable and helps them grow. We can show them their progress with the reports and where they need improvement. This program is all about establishing lifestyle changes to be a healthier person, as well as reversing their diabetes or any of the other issues this program can help with.

I think that was a little clearer. Let me know if you still need help understanding this process.

 

[Lkwdmntr]

I want you to know how grateful I am for all the help you have given me over the last weeks and months. You have saved me numerous times and I know this most recent problem has been a problem.

If you cannot help, I understand. Otherwise, I would genuinely appreciate the opportunity to explain the problem to you differently.

Please let me know either way, and remember I am open to a phone call or screen share to help explain things better.

Thank you!

 

[DHookom]

You need to provide a query with columns for every value you want to display in your report. It should be a totals query that is grouped by the date, as well as the fields you expect to see on the left side of the page. There should be one column that totals the numeric value.

 

[Lkwdmntr]

Hello, Thanks for hanging in there with me. Work has been overwhelming, and I haven't had any time to dedicate to this project. I will try to do better starting next week. Thanks!

 

[DHookom]

 

[Lkwdmntr]

Hello, I finally had a chance to look into this and gather the information you need. I already had something close to what you asked for, but made some tweeks to accommodate the exact details. The following query is under qlkpStandardActionsPointsByLoop_ALLTEST. Here is the query

SELECT tblWeeklyPlans.UserIDFK, tblWeeklyPlans.WeekNumber, tblActions.Action, tblWeeklyPlans.Loop, 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

If you could help turn this into a crosstab, that would be great.

Let me know what you might need next.

I used John Accelerated as the client. He has all twelve weeks' worth of data.

 

[DHookom]

There is no date column, only a WeekNo. Did you want to use the WeekNo as the column heading?

If WeekNo is the column heading, which columns create your Row Headings and which one column/field do you want to use the value?

Can you create and share an Excel that mimics your desired result?

 

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

 

[Lkwdmntr]

Hello, hope you are doing well. You have probably been on a few trips as the summer comes to an end.

I was wondering about the status of the report and whether you might need anything else from me to complete it.

Thanks again for taking the time to help with this.