Crosstab query report

[Rohit Thomas]

Hello,

I have a report based on the following query (see SQL
below).The column headers for the report are created based
on two records [MonthAdjCount] and [MonthAdjAmount] using
field [FldName] from table tblXTabTellerAdj. If there is
no data for a particular month, my report errors out
because the column header name does not exist. For
example, the column headers (MonthAdjCount1,
MonthAdjAmount1, MonthAdjCount2, MonthAdjAmount2, etc) are
created if data for Jan, Feb, etc. exists). I have
constructed the report to show all 12 months, however my
report will error out if no data exists for a particular
month. How do I get around this? I believe I have to use
relative column headers but not sure how to go about doing
this. Any help would be appreciated.

Thanks,
Rohit Thomas

TRANSFORM Sum(IIf([FldName]="MonthAdjCount",[Adj Count],
[Adj Amount])) AS [Adj Count Amount]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Branch Name],
[qryBank001TellerFifty&UnderAll].[Teller ID],
[qryBank001TellerFifty&UnderAll].[First Name],
[qryBank001TellerFifty&UnderAll].[Last Name], Count(IIf
([FldName]="MonthAdjCount",[Credit Or Debit])) AS [Adj
Count Total], Sum(IIf([FldName]="MonthAdjCount",[Adj
Amount])) AS [Adj Amount Total]
FROM [qryBank001TellerFifty&UnderAll], tblXTabTellerAdj,
tblFileDate
WHERE ((([qryBank001TellerFifty&UnderAll].DateYear)=Year
([tblFileDate]![ImpFileDate])))
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Branch Name],
[qryBank001TellerFifty&UnderAll].[Teller ID],
[qryBank001TellerFifty&UnderAll].[First Name],
[qryBank001TellerFifty&UnderAll].[Last Name]
PIVOT [FldName] & Month([FileDate]);

 

[Duane Hookom]

Add all your possible column heading values to the Column Headings property
of the crosstab query.