Crosstab Column Headers

[uaguy3005]

I have a crosstab query I'm using to display sort results for the last 30 days. Row has part/defect information and column has date information. How do I show all the dates in the column header for the last 30 days, including the ones with no data. I've seen several articles on locking the headers down to the month, but cant seem to find anything that would tell how to keep the last 30 days. This would be a rolling 30 days so if I ran the query today it would show 11/16/15 - 12/17/15, if I ran it tomorrow it would show 11/17/15 - 12/18/15.



TRANSFORM Sum(Nz([QtyRejected],0)) AS RejectAmount
SELECT [PartNum] & " - " & [DefectDescription] AS Finding
FROM (tblParts INNER JOIN tblSortData ON tblParts.PartID = tblSortData.PartsID) INNER JOIN (tblDefectCodes INNER JOIN tblRejects ON tblDefectCodes.DefectID = tblRejects.DefectCode) ON tblSortData.SortDataID = tblRejects.SortDataID
WHERE (((DateValue([InspectDateTime]))>=Date()-30))
GROUP BY [PartNum] & " - " & [DefectDescription]
PIVOT DateValue([InspectDateTime]);

[June7]

Producing a field for date when there are no records requires a dataset of all possible date values to be included in the query by joining to the data table.

Or put dummy records in the data table.

Or specify column headings http://allenbrowne.com/ser-67.html#ColHead - not practical for your requirement unless you write VBA code to build the SQL along with QueryDefs to modify the query object

[InsuranceGuy]

Or put dummy records in the data table.

I keep a table of dates handy for this reason. Any database that needs to output dates where there may or may not be data gets this table added to it.

Table: tblDates
Column(s): 1
Column Names: dtValue
Value description: Date values between 1/1/1970 - 1/1/2050