Crosstab query showing months

[Jeff Gilstrap]

I have a crosstab query I am using for a report that has
the abbreviated month as the row. Some of the months have
no values to count and that month does not show up on the
query. The problem is when the report using this query is
previewed, it will not print because it does not recognise
the months that have no counts. The report has the months
hardwired into it. Any suggestions to "work around" this
problem? Thanks Jeff G.

The SQL for the query is as follows:

TRANSFORM Count(tblVendorNotes.Date) AS CountOfDate
SELECT Format([Date],"yyyy") AS [Year],
tblVendorNotes.ContractorID, Suppliers.[Supplier Name],
Customers.Company, tblVendorNotes.CustomerID
FROM Suppliers INNER JOIN (Customers RIGHT JOIN
tblVendorNotes ON Customers.[Customer Number] =
tblVendorNotes.CustomerID) ON Suppliers.[Supplier Number]
= tblVendorNotes.ContractorID
WHERE (((Suppliers.[1099])=-1) AND ((tblVendorNotes.Code)
="dr"))
GROUP BY Format([Date],"yyyy"),
tblVendorNotes.ContractorID, Suppliers.[Supplier Name],
Customers.Company, tblVendorNotes.CustomerID, Suppliers.
[1099], tblVendorNotes.Code
ORDER BY Format([Date],"yyyy"), tblVendorNotes.ContractorID
PIVOT Format([Date],"mmm");

 

[Steve Schapel]

Jeff,

Go to the query properties (right-click anywhere on the background of
the top panel of the query design window), and set the Column Headings
property of the query to include all possible months. Enter it by
putting, for example,...
"Jan","Feb","Mar", etc
This will show in the SQL of the query like this...
.... PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar", etc)

 

[Jeff Gilstrap]

Excellent-Thanks Steve!!!!

-----Original Message-----
Jeff,

Go to the query properties (right-click anywhere on the background of
the top panel of the query design window), and set the Column Headings
property of the query to include all possible months. Enter it by
putting, for example,...
"Jan","Feb","Mar", etc
This will show in the SQL of the query like this...
.... PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar", etc)

--
Steve Schapel, Microsoft Access MVP

I have a crosstab query I am using for a report that has
the abbreviated month as the row. Some of the months have
no values to count and that month does not show up on the
query. The problem is when the report using this query is
previewed, it will not print because it does not recognise
the months that have no counts. The report has the months
hardwired into it. Any suggestions to "work around" this
problem? Thanks Jeff G.

The SQL for the query is as follows:

TRANSFORM Count(tblVendorNotes.Date) AS CountOfDate
SELECT Format([Date],"yyyy") AS [Year],
tblVendorNotes.ContractorID, Suppliers.[Supplier Name],
Customers.Company, tblVendorNotes.CustomerID
FROM Suppliers INNER JOIN (Customers RIGHT JOIN
tblVendorNotes ON Customers.[Customer Number] =
tblVendorNotes.CustomerID) ON Suppliers.[Supplier Number]
= tblVendorNotes.ContractorID
WHERE (((Suppliers.[1099])=-1) AND ((tblVendorNotes.Code)
="dr"))
GROUP BY Format([Date],"yyyy"),
tblVendorNotes.ContractorID, Suppliers.[Supplier Name],
Customers.Company, tblVendorNotes.CustomerID, Suppliers.
[1099], tblVendorNotes.Code
ORDER BY Format([Date],"yyyy"), tblVendorNotes.ContractorID
PIVOT Format([Date],"mmm");

.