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

[Lkwdmntr]

You didn't have much for weeks in your data. Are you looking for a record source like this?
View attachment 116485

I got this using a query with the SQL of

TRANSFORM Sum(Abs([IsActivity])) AS Expr1
SELECT tblActions.Action, tblWeeklyPlans.UserIDFK, tblWeeklyPlans.Loop
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,
  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");

The report based on the above SQL would look like the following with the control sources in the detail section bound to W0,W1,W2,...
View attachment 116487

 

[Lkwdmntr]

That looks great. It is almost what I need. Now, I just need to understand it and apply it. I have another report that does the same thing, and I need to apply this fix to it. The only thing is that I am not pulling the exact data; I am just acknowledging that there was an action for that week and using an "X" to represent there was one. The query you used replaced the "qrptStandardActionsByWeek_Crosstab", right?

 

[DHookom]

The report's record source is qrptStandardActionsByWeek. The issue I had was understanding your specification/requirement/data. I spent a fair amount of time reviewing your data and objects to try determine what you wanted.

My suggestion is to review the simplest form of my solution in the Calendar Report I attached earlier or the tek-tips link. The key is to have a starting date/week/month/year and use that value to group other records based on the date in the record and the start value. The DateDiff() function is used to determine in which group (column) the record should appear.

 

[DHookom]

There seems to be a fair amount of redundancy in your objects that I would clean up such as the following. From what I could tell looking at a few of these, the only changes are a value in the where clause. Objects need to be normalized just as data tables need to be normalized. (this is maybe a topic for another time)

 

[Lkwdmntr]

There seems to be a fair amount of redundancy in your objects that I would clean up such as the following. From what I could tell looking at a few of these, the only changes are a value in the where clause. Objects need to be normalized just as data tables need to be normalized. (this is maybe a topic for another time)

View attachment 116560

 

[Lkwdmntr]

Yes, I have many reports with too many rows to do as a crosstab. It seems to be an issue with Access. I am self-taught and struggle with this, but I feel I've done well. You should have seen my first version of this. The tables were a mess. Is there any place or person that could do a Zoom with me to better understand the "normalizing" of the data?

 

[DHookom]

Most of us are embarrassed by our earliest attempts to create applications. I don't see much wrong with your table structure without a full deep dive in specifications. I like your use of primary and foreign keys that eliminate storing redundant data as well as implementation of a naming convent . I would question your pairs of fields like: StartTimeChallengeYes and StartTimeChallengeNo. I wonder if this could be combined into a single field but I'm not fully aware of their usage.

My "object normalization" refers to many queries, forms, and reports with number or day of the week suffixes. Again, I haven't reviewed many of them but if all you are changing is something simple like just filtering different records, these could be combined.

There are lots of on-line resources if you want to hone your table normalization skills.