Odd... Is this ok...?

[BLeslie88]

Listen up... This works, but worries me. If anyone understands this please let me know if it is normal and / or ok..?

Query:
TRANSFORM Sum([tblOPOP].[Qty]) AS data
SELECT [tblOPOP].[Div], [tblOPOP].

FROM tblOPOP
WHERE Mid([tblOPOP].[Period],3,4)  Between  "0349" and "0352"
GROUP BY [tblOPOP].[Div], [tblOPOP].[Code]
PIVOT (Mid([tblOPOP].[Period],3,4)  Between  "0349" and "0352");

Results:
Div	Code	                   -1
B	23070007	420

Now, the results are good but alas the -1 field title bothers me and I can't apparently change it. I want these results, and the code works... Is this ok, does anyone know..? 
~~~~~  :confused: :confused: :confused: ~~~~~

 

[Jon K]

I think it is OK.

Your query asks Access to pivot on:-
(Mid([tblOPOP].[Period],3,4) Between "0349" and "0352")

Access interprets it as the requirement of two columns: one for those falling within "0349" and "0352" and another for those falling without. And Access uses the heading -1 to stand for True and 0 to stand for False as the column headings. Since you have limited the selection in the Where clause to only those records falling within the range, you will get only the True column i.e. -1.

If you like, you can create a new query based on your query to change the column heading:-

SELECT Div, Code, [-1] as SumOfQty
FROM yourQuery;