Need to print a crosstab report without complete data

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

 

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

 

[Pat Hartman]

I don't want anyone to do the work for me;

And yet you are asking for private consulting services. If your company will pay to get you support, that is probably the fastest/best solution. Otherwise, you should plan on working through the problems one at a time with all of us helping. Just be careful to answer specific questions and it always helps to provide images of error messages and at least a db with your schema defined.

 

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