Crosstab Returns Empty Fields


T

thehikingfool

What am I doing wrong here?
Here is the SQL:

TRANSFORM Sum(Query3SalesConvert.SALES) AS SumOfSALES1
SELECT Query3SalesConvert.MANUFACTURERNAME,
Query3SalesConvert.CREATETERRITORYID, Sum(Query3SalesConvert.SALES) AS
SumOfSALES
FROM Query3SalesConvert
WHERE ((Year([ENTRYDATE])=Year(Date())))
GROUP BY Query3SalesConvert.MANUFACTURERNAME,
Query3SalesConvert.CREATETERRITORYID
PIVOT Format([ENTRYDATE],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");


If this post is a duplicate I apoligize, I didn't see my original post listed.
 
Ad

Advertisements

V

vanderghast

If your regional setting is not in English, the 3 letters month names may
differ than the ones you supplied.

If there is not data for a given month for the current year (ex: no data for
December, yet), then that column will be entirely blank, since there is no
data to sum.


Vanderghast, Access MVP
 
Ad

Advertisements

T

thehikingfool

Sorry, this is a duplicate post.
Original post can be followed under 'Crosstab returns blank fields'

vanderghast said:
If your regional setting is not in English, the 3 letters month names may
differ than the ones you supplied.

If there is not data for a given month for the current year (ex: no data for
December, yet), then that column will be entirely blank, since there is no
data to sum.


Vanderghast, Access MVP



thehikingfool said:
What am I doing wrong here?
Here is the SQL:

TRANSFORM Sum(Query3SalesConvert.SALES) AS SumOfSALES1
SELECT Query3SalesConvert.MANUFACTURERNAME,
Query3SalesConvert.CREATETERRITORYID, Sum(Query3SalesConvert.SALES) AS
SumOfSALES
FROM Query3SalesConvert
WHERE ((Year([ENTRYDATE])=Year(Date())))
GROUP BY Query3SalesConvert.MANUFACTURERNAME,
Query3SalesConvert.CREATETERRITORYID
PIVOT Format([ENTRYDATE],"mmm") In
("JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC");


If this post is a duplicate I apoligize, I didn't see my original post
listed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top