What Karl's suggesting is change that to
TRANSFORM Sum([3QrySMICcodessumsum].SumOfAMT_RECV) AS
SumOfSumOfAMT_RECV
SELECT [VEN_UPC_NUM_SYS] & [VEN_UPC_MANUF] & [VEN_UPC_ITEM] AS UPC,
[3QrySMICcodessumsum].PCC_SMIC, [3QrySMICcodessumsum].CORP_ITEM_CD,
[3QrySMICcodessumsum].DESC_ITEM, [3QrySMICcodessumsum].VEND_NUM,
[3QrySMICcodessumsum].[NAME], [3QrySMICcodessumsum].SIZEE,
[3QrySMICcodessumsum].SIZE_UOM, [3QrySMICcodessumsum].PACK_WHSE,
Sum([3QrySMICcodessumsum].TOTAL) AS SumOfTOTAL,
Sum([3QrySMICcodessumsum].SumOfSPEND) AS SumOfSumOfSPEND,
Sum([3QrySMICcodessumsum].SumOfAMT_RECV) AS [Total Of SumOfAMT_RECV]
FROM 3QrySMICcodessumsum
GROUP BY [VEN_UPC_NUM_SYS] & [VEN_UPC_MANUF] & [VEN_UPC_ITEM],
[3QrySMICcodessumsum].PCC_SMIC, [3QrySMICcodessumsum].CORP_ITEM_CD,
[3QrySMICcodessumsum].DESC_ITEM, [3QrySMICcodessumsum].VEND_NUM,
[3QrySMICcodessumsum].[NAME], [3QrySMICcodessumsum].SIZEE,
[3QrySMICcodessumsum].SIZE_UOM, [3QrySMICcodessumsum].PACK_WHSE
PIVOT [3QrySMICcodessumsum].[MONTH] IN ("JAN", "FEB", "MAR", "APR", ... ,
"DEC");
(Assuming that MONTH contains the three letter abbreviation for the month.
If it contains something else, that's the list you'd put in the parentheses
as the end).
Incidentally, you really should rename some of your fields. Both Name and
Month are reserved words, and using reserved words for your own purposes can
lead to problems. For a comprehensive list of names to avoid, see what Allen
Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
If you cannot (or will not) rename the fields, at least put square brackets
around them, as I did above.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Here is my SQL:
TRANSFORM Sum([3QrySMICcodessumsum].SumOfAMT_RECV) AS
SumOfSumOfAMT_RECV
SELECT [VEN_UPC_NUM_SYS] & [VEN_UPC_MANUF] & [VEN_UPC_ITEM] AS UPC,
[3QrySMICcodessumsum].PCC_SMIC, [3QrySMICcodessumsum].CORP_ITEM_CD,
[3QrySMICcodessumsum].DESC_ITEM, [3QrySMICcodessumsum].VEND_NUM,
[3QrySMICcodessumsum].NAME, [3QrySMICcodessumsum].SIZEE,
[3QrySMICcodessumsum].SIZE_UOM, [3QrySMICcodessumsum].PACK_WHSE,
Sum([3QrySMICcodessumsum].TOTAL) AS SumOfTOTAL,
Sum([3QrySMICcodessumsum].SumOfSPEND) AS SumOfSumOfSPEND,
Sum([3QrySMICcodessumsum].SumOfAMT_RECV) AS [Total Of SumOfAMT_RECV]
FROM 3QrySMICcodessumsum
GROUP BY [VEN_UPC_NUM_SYS] & [VEN_UPC_MANUF] & [VEN_UPC_ITEM],
[3QrySMICcodessumsum].PCC_SMIC, [3QrySMICcodessumsum].CORP_ITEM_CD,
[3QrySMICcodessumsum].DESC_ITEM, [3QrySMICcodessumsum].VEND_NUM,
[3QrySMICcodessumsum].NAME, [3QrySMICcodessumsum].SIZEE,
[3QrySMICcodessumsum].SIZE_UOM, [3QrySMICcodessumsum].PACK_WHSE
PIVOT [3QrySMICcodessumsum].MONTH;
Post the SQL of your query. Open the query in design view, click on
menu
VIEW - SQL View. This opens another window. Highlight all, copy, and
paste
in a post.
--
KARL DEWEY
Build a little - Test a little
Where do I put that code in the query? In a new field in criteria?
On Jun 6, 4:33 pm, KARL DEWEY <
[email protected]>
wrote:
Your pivot must match.
PIVOT Format([YourDateField], "mmm") IN("JAN", "FEB", "MAR", "APR",
etc, );
:
I have a crosstab where the values are listed in months. It works
fine
but the months reflect 1,2,3,4, etc I would like Jan, Fed, Mar,
Apr,
etc. I read how to go into query properties and add column headers
of
"JAN", "FEB", "MAR", "APR", etc, but when I run the query it says
data
type mismatch. Any thoughts?
