Crosstab query problem (1 Viewer)

Lkwdmntr

Registered User.
Local time
Yesterday, 17:34
Joined
Jul 10, 2019
Messages
261
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

error reading drive A:
Local time
Today, 08:34
Joined
May 7, 2009
Messages
12,703
you Create Dynamic report that adjust the Columns to whatever available column is available in the crosstab.
 

arnelgp

error reading drive A:
Local time
Today, 08:34
Joined
May 7, 2009
Messages
12,703
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: 25

Lkwdmntr

Registered User.
Local time
Yesterday, 17:34
Joined
Jul 10, 2019
Messages
261
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
日韩精品亚洲专区在线影院