Looking for help on this one, hoping it's a simple solution for the more experienced users.
I have a crosstab query that is part of a production report screen and we review this data on a week by week basis. From the Screen Dump in the attachment you will see that the query returns a count of thiese products for all weeks except wk 38. We didn't produce anything that week, so I need to show a zero instead of ignoring the week - I've highlighted weeks 39 to 37 in the image.
Here is the SQL used
TRANSFORM Count(qryDataFrames.Moulded) AS CountOfMoulded
SELECT qryDataFrames.Process, Count(qryDataFrames.Moulded) AS Total
FROM qryDataFrames
WHERE (((qryDataFrames.MouldedOn) Between (Date()-28) And Date()))
GROUP BY qryDataFrames.Process
ORDER BY Format([Date],"yy-ww") DESC
PIVOT Format([Date],"yy-ww");
Hope you can help?
I have a crosstab query that is part of a production report screen and we review this data on a week by week basis. From the Screen Dump in the attachment you will see that the query returns a count of thiese products for all weeks except wk 38. We didn't produce anything that week, so I need to show a zero instead of ignoring the week - I've highlighted weeks 39 to 37 in the image.
Here is the SQL used
TRANSFORM Count(qryDataFrames.Moulded) AS CountOfMoulded
SELECT qryDataFrames.Process, Count(qryDataFrames.Moulded) AS Total
FROM qryDataFrames
WHERE (((qryDataFrames.MouldedOn) Between (Date()-28) And Date()))
GROUP BY qryDataFrames.Process
ORDER BY Format([Date],"yy-ww") DESC
PIVOT Format([Date],"yy-ww");
Hope you can help?