Need to print a crosstab report without complete data

[Lkwdmntr]

Hello, I have a report that uses a crosstab query for 12 weeks. I would like to monitor the data even after a few weeks, but I get an error when running it without the data for the full 12 weeks.

When I run the query separately, it works fine, but the report is set up for all 12 weeks. Is there a way I can get it to print just blank for the weeks I don't have?

 

[xavier.batlle]

Hello, I have a report that uses a crosstab query for 12 weeks. I would like to monitor the data even after a few weeks, but I get an error when running it without the data for the full 12 weeks.

When I run the query separately, it works fine, but the report is set up for all 12 weeks. Is there a way I can get it to print just blank for the weeks I don't have?

I thing you'll need a dummy table with 52 rows, one for each week of the year and join it with your data.

 

[theDBguy]

Can you post a screenshot of the report and query results? Thanks.

 

[DHookom]

Create your crosstab query with relative time periods as column headings. Modify the following to use weeks rather than months.
Try not to use "absolute" column headings for dates. You could possibly use "relative" dates. For instance:

Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate

You want to show 12 months of sales in columns of a crosstab report.

Set the parameter data type with the menu: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
You should be able to substitute other date intervals for months such as "q" for quarter or "d" for day.

This solution requires no code and will run fairly quickly.

 

[DHookom]

For a crosstab report with date, week, month, quarter, or year headings there is no need for a temporary table or code. Check my reply or the FAQ at https://www.tek-tips.com/forums/703/faqs/5466

 

[Gasman]

And we are at 102 Duane.

 

[DHookom]

Here is a demo I just modified to add a date interval crosstab by day. It would be simple to take the concept and change to any interval.

 

[DHookom]

You can delete all the data for one of the dates in my example and the column will still render.

 

[DHookom]

The OP stated “12 weeks”. I’ve made it clear in every of my replies the solution I suggest is for date intervals. I maybe should have added “consecutive date intervals”.

I have other examples for text column headings and they involve a temporary table and code.

 

[DHookom]

I was simply agreeing with you.

 

[DHookom]

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.

 

[Lkwdmntr]

Can you post a screenshot of the report and query results? Thanks.

Hey there. I remember you from a while back. I was doing some testing and realized that I can't access this report without data for the entire Loop (12 weeks). I am posting screenshots of the crosstab query result, the error I get when running the report, and a screenshot of a report that ran with complete data. I can also include a copy of the database. Open the Main Menu, Select John Accelerated, Reports, Standard Actions by Week Plan, and Output.

I tried using some of the other advice but couldn't seem to make it work. I would be most grateful if you could take a look and help find a solution.

 

[DHookom]

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

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

 

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