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

Lkwdmntr

Registered User.
Local time
Yesterday, 19:12
Joined
Jul 10, 2019
Messages
306
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?
 
You have to force the crosstab to return 12 weekly fields. I can post an example that does the same thing for years that you may be able to adapt for your use.

 
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.
 
Can you post a screenshot of the report and query results? Thanks.
 
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.
 
The sample for the variable column headings involves making a temp table with all the projected column headings and then using a left join to get the headings into the crosstab. You still need to use some code to populate the headers on the report.
 
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.
 

Attachments

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
Your crosstab has 5 fixed columns. The purpose of the table in my example is to ensure that the crosstab query ALWAYS contains a specific number of columns regardless of whether or not there is any data for one of the columns. The issue of the OP is not the naming of the controls but the fact that the crosstab does not always contain all the necessary columns.
 
You can delete all the data for one of the dates in my example and the column will still render.
 
Your form works because you added a column heading for each column. If you can't predefine the column headings, you can't use this technique.

For example, if your column headings are expense types, not all properties have a swimming pool so the report wouldn't necessarily include the Pool expense. OR if you ran the report for the winter months, there would be no pool expense in the data for those months.
 
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.
 
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.
 
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.
 

Attachments

  • ReportWithFullData.png
    ReportWithFullData.png
    48 KB · Views: 52
  • ResultQuery.png
    ResultQuery.png
    26.5 KB · Views: 53
  • RptError.png
    RptError.png
    11.4 KB · Views: 57
  • FettlerHealthDBJuly2024.zip
    FettlerHealthDBJuly2024.zip
    4.2 MB · Views: 57
OK, did you look at the sample I posted? As I tried to explain, it solves this problem. There may be simpler ways also. Duane's sample uses the columns property. That is pretty simple but has limitations in certain situations. For yours, it is probably fine.
 
You didn't have much for weeks in your data. Are you looking for a record source like this?
1728752729943.png


I got this using a query with the SQL of
Code:
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,...
1728753084398.png
 

Users who are viewing this thread

Back
Top Bottom