Crosstab returns blank fields

T

thehikingfool

What am I missing?
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");
 
J

John Spencer

Would you care to explain which fields are blank? I would guess you are
referring to the MONTH columns. And I would guess that Dec and perhaps Nov
columns are blank since they have had no sales recorded in the database for
the current year.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
T

thehikingfool

All fields are blank.
If I remove the criteria for the year; WHERE
((Year([ENTRYDATE])=Year(Date()))), then it returns sales as expected but it
includes sales from all years. I want to limit this to sales for the current
year.

KARL DEWEY said:
What fields are blank?
What do you get if you remove the criteria?

--
Build a little, test a little.


thehikingfool said:
What am I missing?
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");
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this test --
SELECT Year([ENTRYDATE]) AS YearEntryDate, Year(Date()) AS YearDate,
Format([ENTRYDATE], "mmm") AS MMMEntryDate
FROM Query3SalesConvert
ORDER BY [ENTRYDATE];

See if you get what you expect.

--
Build a little, test a little.


thehikingfool said:
All fields are blank.
If I remove the criteria for the year; WHERE
((Year([ENTRYDATE])=Year(Date()))), then it returns sales as expected but it
includes sales from all years. I want to limit this to sales for the current
year.

KARL DEWEY said:
What fields are blank?
What do you get if you remove the criteria?

--
Build a little, test a little.


thehikingfool said:
What am I missing?
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");
Click to expand...
Click to expand...
Click to expand...
 
T

thehikingfool

Where should I enter this?
I've tried it in several places and get blanks again or a message stating
the query does not include the specified expression 'Year([ENTRYDATE])' as
part of an aggregate function.

KARL DEWEY said:
Try this test --
SELECT Year([ENTRYDATE]) AS YearEntryDate, Year(Date()) AS YearDate,
Format([ENTRYDATE], "mmm") AS MMMEntryDate
FROM Query3SalesConvert
ORDER BY [ENTRYDATE];

See if you get what you expect.

--
Build a little, test a little.


thehikingfool said:
All fields are blank.
If I remove the criteria for the year; WHERE
((Year([ENTRYDATE])=Year(Date()))), then it returns sales as expected but it
includes sales from all years. I want to limit this to sales for the current
year.

KARL DEWEY said:
What fields are blank?
What do you get if you remove the criteria?

--
Build a little, test a little.


:

What am I missing?
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");
Click to expand...
Click to expand...
Click to expand...
 
T

thehikingfool

Boy, am I embarassed and sorry for wasting anybodies time here.

In one of the queries that I based my Query3SalesConvert on I had entered
criteria for Year([ENTRYDATE]) = Year(Date()) - 1. I was trying to filter
data from last year as I tested other queries. I forgot to remove it before
creating the crosstab.

Everything works perfectly now.

Again, sorry for the confusion.

thehikingfool said:
Where should I enter this?
I've tried it in several places and get blanks again or a message stating
the query does not include the specified expression 'Year([ENTRYDATE])' as
part of an aggregate function.

KARL DEWEY said:
Try this test --
SELECT Year([ENTRYDATE]) AS YearEntryDate, Year(Date()) AS YearDate,
Format([ENTRYDATE], "mmm") AS MMMEntryDate
FROM Query3SalesConvert
ORDER BY [ENTRYDATE];

See if you get what you expect.

--
Build a little, test a little.


thehikingfool said:
All fields are blank.
If I remove the criteria for the year; WHERE
((Year([ENTRYDATE])=Year(Date()))), then it returns sales as expected but it
includes sales from all years. I want to limit this to sales for the current
year.

:

What fields are blank?
What do you get if you remove the criteria?

--
Build a little, test a little.


:

What am I missing?
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");
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Crosstab Returns Empty Fields 2
UNION and Crosstab query 5
Custom Format for crosstab headings 2
Group by Range in Crosstab Query 4
Showing Crosstab Totals Column in Chart 1
Crosstab qry not adding up 1
Sorting Crosstab Query 2
CrossTab Query Format 2

Top