Query question

[acin0026]

I have a table which has field and Data below:
TransactionNo SellDate Product Amount
001 01/01/01 A 5
002 01/01/01 A 6
003 01/02/01 B 7
004 02/04/01 A 3
005 02/05/01 C 8

I would like to create a report which group by MONTH, PRODUCT and show sum of Amount sold. What should I do? I tried to create by crosstab query but it only group by Date (not by MONTH) for each product (may be I did something wrong.). Or I need VB.

Thanks for your help in advance.

 

[braindead2]

Crosstab should do it and it will allow you to group by month. Try it again

 

[blakex1]

In the QBE pane (query design window) you need to make sure you have set the "Groupby" line for both of the fields that you want to use. The order that you put the fields in makes a difference too. It will break down the groups by each field that it comes to that has the Groupby option set.

Secondly, to create a sum, you will want to create a "calculated field" in one of the blank columns. Just drag the [Amount] field to the blank column and use a different name for it, the syntax should be
AMOUNTSUM: Amount
then go up to the toolbar and click on the button with the "summation character" (greek capital sigma character)
this brings up a new line in the QBE pane that you can set to "Sum"
by doing this, you have set the query to create a sum of all the values in the "amount" field.

 

[acin0026]

Thank you for your answer.

It seems to be my question is not clear enough. QBE allows me to group by date and Sum. It lets me group by only day. Also, I can group by month if my criteria has >=01/01/01 and <02/01/01. However, It let me group only one month for each query. So, If I need to group by every month, I need 12 queries to do that. Do you have the way that group by Month?

 

[braindead2]

when you click on query tab - new do you have the crosstab query wizard as an option ?
if so
have product as your row source
sell date as your column it will give you the option to group by. Select month
and sum on your product amount as the value
the SQL should look like this

TRANSFORM Sum([amount]) AS [The Value]
SELECT [product], Sum([amount]) AS [Total Of amount]
FROM Table1
GROUP BY [product]
PIVOT Format([selldate],&quot;mmm&quot IN (&quot;Jan&quot;,&quot;Feb&quot;,&quot;Mar&quot;,&quot;Apr&quot;,&quot;May&quot;,&quot;Jun&quot;,&quot;Jul&quot;,&quot;Aug&quot;,&quot;Sep&quot;,&quot;Oct&quot;,&quot;Nov&quot;,&quot;Dec&quot;
replace your table name with &quot;table1 in above code