SQL too long

[mgp]

I need some help with a2k reports.

I've tried different methods of reporting but I either
have too long SQL stmt or errors in the results.

I'm trying to report a summary of sales during the last
52 weeks for each customer. My table consist of:

Orders
______
CustName
TransDate
QtySold
UnitPrice

My report needs to summarize the QtySold for each week in
the last 52 weeks to show patterns. This is what I expect
to see as results:

CoABC 3 4 1 2 4 9 11 4 2 0 0 ...
CoDEF 0 1 2 0 4 6 13 6 3 1 1 ...
....

If I try to create a SQL stmt defining all 52 fields, I
get an error that it is too long. If I define the fields
in the Detail section of the report and print them in the
category CustName of the report, I get 3e+01 when nothing
should be showing. I've tried different ways but can't
seem to get it going. Out of frustration, I've tried Dsum
but it really slows it down and I still get the errors.
For example =IIf([Count or Dollars (c/d)]="c",DSum
("[QtySold]/24","Orders","DatePart('ww',[TransDate])
=1"),DSum("[QtySold]*[UnitPrice]","Orders","DatePart('ww',
[TransDate])=1"))

Help?!

 

[Allen Browne]

You should be able to do this with a Crosstab query.

A crosstab takes the value from one field and turns them into column
headings. You are after the week number of the TransDate field as your
column headings.

1. Create a new query. (No table needed yet.)

2. Switch it to SQL View (View menu).

3. Paste this in:

TRANSFORM Sum(Orders.QtySold) AS SumOfQtySold
SELECT Year([TransDate]) AS WhichYear, Orders.CustName
FROM Orders
WHERE (Orders.TransDate Between #1/1/2000# And #12/31/2010#)
GROUP BY Year([Orders].[TransDate]), Orders.CustName
PIVOT DatePart("ww",[Orders].[TransDate]) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,
29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53);

4. Switch back to Design View. Check it works. Adjust or remove the WHERE
clause as desired.

5. Build a report based on this query.

Because the field names are numbers, use square brackets around them on your
report.

Because some of the week numbers may not be present, it is important to
include them in the Column Headings property of your query so that they
exist for the report.