Crosstab query showing months

  • Thread starter Thread starter Jeff Gilstrap
  • Start date Start date
J

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");
 
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)
 
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


Jeff said:
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");
Click to expand...
.
Click to expand...
 
Back
Top