Adding specific Columns in a query/report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have set up the following Cross Tab query

PARAMETERS [Enter Customer] Text ( 255 );
TRANSFORM Sum([Main Table].Quanity) AS SumOfQuanity
SELECT [Main Table].Customer, [Main Table].Spec, [Main Table].Lot, [Main
Table].sm, Sum([Main Table].Quanity) AS [Total Of Quanity]
FROM [Main Table]
WHERE ((([Main Table].Customer)=[Enter Customer]))
GROUP BY [Main Table].Customer, [Main Table].Spec, [Main Table].Lot, [Main
Table].sm
PIVOT Format([Date],"ww") 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);

I am trying to generate a report based on this query. I need the report to
read by week. Obviously 53 weeks is too much for one page so I have set up
the report to show 1 Qtr per page. I would like to be able to sum [Quanity]
for each [Spec] for each page or in other words each qrt. I imagine that I
should do this in the query somehow but dont know how to do it. Any
suggestions?
 
Toldoriath said:
I have set up the following Cross Tab query

PARAMETERS [Enter Customer] Text ( 255 );
TRANSFORM Sum([Main Table].Quanity) AS SumOfQuanity
SELECT [Main Table].Customer, [Main Table].Spec, [Main Table].Lot, [Main
Table].sm, Sum([Main Table].Quanity) AS [Total Of Quanity]
FROM [Main Table]
WHERE ((([Main Table].Customer)=[Enter Customer]))
GROUP BY [Main Table].Customer, [Main Table].Spec, [Main Table].Lot, [Main
Table].sm
PIVOT Format([Date],"ww") 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);

I am trying to generate a report based on this query. I need the report to
read by week. Obviously 53 weeks is too much for one page so I have set up
the report to show 1 Qtr per page. I would like to be able to sum [Quanity]
for each [Spec] for each page or in other words each qrt. I imagine that I
should do this in the query somehow but dont know how to do it. Any
suggestions?
Click to expand...

Why don't you try formatting the report in Landscape mode & reducing the
font size of all the column controls, before messing w/ the query?
 
If all else fails, create 4 reports, one for each qtr. There are other
ways, but this one is probably the quickest. Just make sure you settle on
the features before starting to copy and paste.
 
On page would require a font of about 2 so not realistic. Currently the
report is already 4 pages or actually 4 different reports. However this is
not the question im putting fowarth and would like some help on the question
it self. How do i add rows? Im sure i need to set it up in my query but not
sure how to do it. Any suggestions?
 
Back
Top