SQL too long

  • Thread starter Thread starter mgp
  • Start date Start date
M

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?!
 
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.
 
Allen ... you're fantastic ... Thank you very much!
-----Original Message-----
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,
Click to expand...
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.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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?!
Click to expand...


.
Click to expand...
 
Back
Top