Crosstab query problem (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 09:08
Joined
Jul 10, 2019
Messages
277
I have a couple of crosstab queries for days of the week. My problem is that if, say week 2, has no data. The report doesn't recognize the field and the report doesn't print.

I am making the crosstab query off another query that gets all the records that have a type of "personal" and where the measurement is null. Here's that query.

Code:
SELECT tblWeeklyPlans.UserIDFK, tblWeeklyPlans.Loop, tblMealComponentsPerWeeklyPlan.ComponentType, tblMealComponentsPerWeeklyPlan.FoodItem, tblMealComponentsPerWeeklyPlan.Qty, tblMealComponentsPerWeeklyPlan.Measurement, tblWeeklyPlans.WeekNumber, "X" AS IdX
FROM tblWeeklyPlans INNER JOIN (tblActionsPerWeeklyPlan INNER JOIN tblMealComponentsPerWeeklyPlan ON tblActionsPerWeeklyPlan.ActionsPerWeeklyPlanID = tblMealComponentsPerWeeklyPlan.ActionsPerWeeklyPlanIDFK) ON tblWeeklyPlans.WeeklyPlanID = tblActionsPerWeeklyPlan.WeeklyPlanIDFK
WHERE (((tblWeeklyPlans.UserIDFK)=glngUserID()) AND ((tblMealComponentsPerWeeklyPlan.ComponentType)="Personal") AND ((tblMealComponentsPerWeeklyPlan.Measurement) Is Null));


Anyone no how to remedy this. Here's the sql for the crosstab query if needed. If there is a record I want to show an X

Code:
TRANSFORM First(qrptReversalDietByWeek_Personal_UnRationed.IdX) AS FirstOfIdX
SELECT qrptReversalDietByWeek_Personal_UnRationed.UserIDFK, qrptReversalDietByWeek_Personal_UnRationed.Loop, qrptReversalDietByWeek_Personal_UnRationed.FoodItem
FROM qrptReversalDietByWeek_Personal_UnRationed
GROUP BY qrptReversalDietByWeek_Personal_UnRationed.UserIDFK, qrptReversalDietByWeek_Personal_UnRationed.Loop, qrptReversalDietByWeek_Personal_UnRationed.FoodItem
PIVOT qrptReversalDietByWeek_Personal_UnRationed.WeekNumber;

I have another query that will end up having the same issue if there is no value for that week, which is always possible. I need to program for every possibility.

Thanks, I hope someone can help with this.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
you Create Dynamic report that adjust the Columns to whatever available column is available in the crosstab.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
sample dynamic report for crosstab.

EDIT: its only 14 week, you will go on Creating those "fix" column, then modify your Report to adjust the additional column.
 

Attachments

  • dynamicCrossTabReport.accdb
    616 KB · Views: 237

Lkwdmntr

Registered User.
Local time
Today, 09:08
Joined
Jul 10, 2019
Messages
277
sample dynamic report for crosstab.

EDIT: its only 14 week, you will go on Creating those "fix" column, then modify your Report to adjust the additional column.
Thanks, I will look at it.
 

Users who are viewing this thread

Top Bottom