Crosstab query problem

[Lkwdmntr]

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.

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

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.

 

[bastanu]

Maybe this helps:

How to Fix Missing Fields in a Crosstab Query in Access

www.quackit.com

 

[arnelgp]

you Create Dynamic report that adjust the Columns to whatever available column is available in the crosstab.

 

[Lkwdmntr]

Thanks a bunch, that was easy.

 

[Lkwdmntr]

you Create Dynamic report that adjust the Columns to whatever available column is available in the crosstab.

Thanks, but bastanu's suggestion did the trick.

 

[arnelgp]

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.

 

[Lkwdmntr]

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.