Crosstab Query

[yoritomo]

Hi, I have a crosstab query I have set up, calculating the cost of a product per month

it should look like
1 2 3 4
gek22 £55 £66 £77 £88
er44 £99 $100 £101 £102
tt66 £103 £104 £105 £106


but instead it looks like

1 2 3 4
gek22 £55 £88
gek22 £66
gek22 £77
er44 £99
er44 $100 £102
er44 £101
tt66 £103 £106
tt66 £103
tt66 £104 £105

Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel

Any help on this would be great

Hmm, doesnt want to keep the spacing in it hopefully you can get the idea

 

[Murli]

Can you give the design of your table?

 

[yoritomo]

hmm its kinda hard as for some reason it keeps deleting my spaces so I cant show you what it should be like

but in sql it looks like this

PARAMETERS [Year] Short, [Stock Code] Text ( 255 );
TRANSFORM Sum([Sales History].VALUE_AT_BUYING_PRICE) AS SumOfVALUE_AT_BUYING_PRICE
SELECT [Sales History].ITEM_NUMBER
FROM [Sales History]
WHERE ((([Sales History].ITEM_NUMBER) Like [Stock Code] & "*") AND (([Year])=Year([TRANSACTION_DATE])))
GROUP BY [Year], [Sales History].ITEM_NUMBER
ORDER BY [Sales History].ITEM_NUMBER
PIVOT Month([TRANSACTION_DATE]);

I want it to display the items along the top and down the side singularly with the price totalled, but it will only group them if the price is the same, so if one sold £75, it will group everything that sold at £75, but it wont group it with ones that sold at £125, I want it to group the lot into one line

 

[yoritomo]

Erm, never mind, I deleted a field and it all seems to be working fine now!!