Ordering by month

[Ana]

Hi,

I need to create an annual report which would list the total orders by
customers by month. I created a working query which groups the orders
'count(ORDER_CLIENT) AS Tot_orders' , customers 'CLIENT_NAME' and
'month(ORDER_DATE) AS Month'.



The report should look like this:



JAN FEB etc.



Willy Billy, Inc. 10 8

Nikeys, Inc. 7 11 etc.



I cannot seem to make the report work. In the Month column I get #Error.

Howto list the above data in a report?



TIA

Ana

 

[Fons Ponsioen]

Hi Ana
What you may want to look at is a Crosstab Query.
If you need further help you may want to post back with
some more specific information.
Hope this helps.
Fons

 

[Ana]

Hello, thanks for the reply.
Are you refering to pivot tables? If so, I'm a bit weak in this section.
I'm obtaining the right information in the query but I'm having difficulties
listing it horizontally in a report.

"Fons Ponsioen" <[email protected]> escribió en el mensaje
Hi Ana
What you may want to look at is a Crosstab Query.
If you need further help you may want to post back with
some more specific information.
Hope this helps.
Fons

 

[Fons Ponsioen]

Yes, Crosstab queries are similar and the same in
application as pivot tables in excell.
You may want to take some time to review this and try it a
few times, if you need more help you may want to post back
with some specific information like the names from the
data from your query and maybe post a little sample data
how you have it now and how you wish it to look in the end.
Fons

 

[Ana]

Hi,
This' actually my simple query:

SELECT dbo.CLIENTS.CLIENT_NAME, COUNT(dbo.ORDERS.ORDER_NUMBER) AS
T_ORDERS,
MONTH(dbo.ORDERS.DATE_IN) AS Month --the date format
stored is yyyymmdd
FROM dbo.CLIENTS INNER JOIN
dbo.ORDERS ON dbo.CLIENTS.CLIENT_ID =
dbo.ORDERS.CLIENT_ID
GROUP BY dbo.CLIENTS.CLIENT_NAME, MONTH(dbo.ORDERS.DATE_IN)

With the query I'm obtanaing the client's name, the month's # and the total
orders for the month.

I'm having problems ordering the above information horizontally in a report,
meaning:

JAN FEB MAR
CLIENT_NAME T_ORDERS T_ORDERS T_ORDERS

Hope this helps

"Fons Ponsioen" <[email protected]> escribió en el mensaje
Yes, Crosstab queries are similar and the same in
application as pivot tables in excell.
You may want to take some time to review this and try it a
few times, if you need more help you may want to post back
with some specific information like the names from the
data from your query and maybe post a little sample data
how you have it now and how you wish it to look in the end.
Fons

 

[Fons Ponsioen]

Ana.
It is realy easy to change the query you have to a
crosstab query.
Here is the SQL:
TRANSFORM Count(XOrders.Order_Number) AS T_Order
SELECT XClients.Client_Name
FROM XClients INNER JOIN XOrders ON XClients.Client_ID =
XOrders.Client_ID
GROUP BY XClients.Client_Name
PIVOT Format([Date_In],"m");
Hope this helps
Fons

-----Original Message-----
Hi,
This' actually my simple query:

SELECT dbo.CLIENTS.CLIENT_NAME, COUNT (dbo.ORDERS.ORDER_NUMBER) AS
T_ORDERS,
MONTH(dbo.ORDERS.DATE_IN) AS Month --the date format
stored is yyyymmdd
FROM dbo.CLIENTS INNER JOIN
dbo.ORDERS ON dbo.CLIENTS.CLIENT_ID

=

 

[Ana]

Thank you Fons,
However I get the following errors using the SQL query Analyzer:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XOrders'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'PIVOT'.

It appears that SQL has a problem with TRANSFORM and FORMAT.

Any ideas?


"Fons Ponsioen" <[email protected]> escribió en el mensaje
Ana.
It is realy easy to change the query you have to a
crosstab query.
Here is the SQL:
TRANSFORM Count(XOrders.Order_Number) AS T_Order
SELECT XClients.Client_Name
FROM XClients INNER JOIN XOrders ON XClients.Client_ID =
XOrders.Client_ID
GROUP BY XClients.Client_Name
PIVOT Format([Date_In],"m");
Hope this helps
Fons

 

[Guest]

Sorry Anna, I had added an X to my tables so as notto
confuse it with other tables. Try:
TRANSFORM Count(XOrders.Order_Number) AS T_Order
SELECT Clients.Client_Name
FROM Clients INNER JOIN Orders ON Clients.Client_ID =
Orders.Client_ID
GROUP BY Clients.Client_Name
PIVOT Format([Date_In],"m");
Hope this helps.
Fons

-----Original Message-----
Thank you Fons,
However I get the following errors using the SQL query Analyzer:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XOrders'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'PIVOT'.

It appears that SQL has a problem with TRANSFORM and FORMAT.

Any ideas?


"Fons Ponsioen" <[email protected]> escribió en el mensaje
Ana.
It is realy easy to change the query you have to a
crosstab query.
Here is the SQL:
TRANSFORM Count(XOrders.Order_Number) AS T_Order
SELECT XClients.Client_Name
FROM XClients INNER JOIN XOrders ON XClients.Client_ID =
XOrders.Client_ID
GROUP BY XClients.Client_Name
PIVOT Format([Date_In],"m");
Hope this helps
Fons

-----Original Message-----
Hi,
This' actually my simple query:

SELECT dbo.CLIENTS.CLIENT_NAME, COUNT (dbo.ORDERS.ORDER_NUMBER) AS
T_ORDERS,
MONTH(dbo.ORDERS.DATE_IN) AS Month --the date format
stored is yyyymmdd
FROM dbo.CLIENTS INNER JOIN
dbo.ORDERS ON dbo.CLIENTS.CLIENT_ID =
dbo.ORDERS.CLIENT_ID
GROUP BY dbo.CLIENTS.CLIENT_NAME, MONTH (dbo.ORDERS.DATE_IN)

With the query I'm obtanaing the client's name, the month's # and the total
orders for the month.

I'm having problems ordering the above information horizontally in a report,
meaning:

JAN FEB MAR
CLIENT_NAME T_ORDERS T_ORDERS T_ORDERS

Hope this helps

"Fons Ponsioen" <[email protected]> escribió en el mensaje
Yes, Crosstab queries are similar and the same in
application as pivot tables in excell.
You may want to take some time to review this and try it a
few times, if you need more help you may want to post back
with some specific information like the names from the
data from your query and maybe post a little sample data
how you have it now and how you wish it to look in the end.
Fons


.

.