[AccessD] Days of the Week Report

Larry,

    You need a crosstab query as the record source for your report. However, your existing table design is not amenable to creation of the final query in a single shot. Under the circumstances, two alternatives can be considered.

    (1) Modify the table design.
        1.1 If too much data has not already been filled in, the table design can be modified so as to replace the existing number type fields (KgsProduced, BatchSize, BagsProduced, -- etc), by just two fields, i.e. Item (text type) and Output (number type). 
        1.2 For each date, there will be separate records for KgsProduced, BatchSize, BagsProduced etc (all in the column named Item) and corresponding numbers will be entered in the column named Output.
        1.3 With this arrangement, creation of crosstab query is straightforward, using Item as Row-Heading, WeekdayName as Column-Heading and Output as Value.

    (2) Managing with existing table structure.
        2.1 If you feel compelled to stick to the existing table design, you have to create individual crosstab queries for KgsProduced, BatchSize, BagsProduced etc, inserting Item as independent alias, to serve as row heading.
        2.2 All crosstab queries created as per 2.1 above, are then to be combined into a union query to serve as record source for the intended report.
        2.3 As an illustration, SQL for crosstab query pertaining to BagsProduced, is given below. Other queries (for KgsProduced, BatchSize -- etc) can be built on similar lines.

    You should now be in a position to proceed ahead with the alternative best suited to your specific situation. If you are stuck for any reason (even after trying for the next few days), just let me know.

Regards,
A.D.Tejpal
--------------

=====================================
TRANSFORM Sum(tblProductionInfo.BagsProduced) AS [OutPut]
SELECT "BagsProduced" AS Item
FROM tblProductionInfo
GROUP BY "BagsProduced"
PIVOT WeekdayName(Weekday([DateProduced]),True) In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");
=====================================

  ----- Original Message ----- 
  From: Lawrence Mrazek 
  To: 'Access Developers discussion and problem solving' 
  Sent: Thursday, September 30, 2004 02:06
  Subject: [AccessD] Days of the Week Report 


  Hi:

  I know I'm missing something, but I currently can't get the following to work:

  1. tblProductionInfo has the following fields: (ProductionID, DateProduced, KgsProduced, BatchSize, BagsProduced ... Etc.)

  Basically, I want to be able to display a report with the days of the week at the top (column headings), and the fields (KgsProduced, BatchSize, BagsProduced) as row headings. 

  Thus, it should look something like:

  Sun Mon Tues Wed Thur Fri Sat
  KgsProduced 1 3 3 2 2 2 1
  BatchSize 1 3 3 2 2 2 1
  BagsProduced 1 3 3 2 2 2 1
  Totals 3 9 9 6 6 6 3

  Can I do this in Access? I'll be using a parameter in the query to select only one calendar week at a time using the DatePart and Year function to filter against user supplied criteria. 

  Any hints would be appreciated ... Haven't worked with Crosstabs in a long time ... I know it has to be easier than I'm making it !

  Larry Mrazek
  LCM Research, Inc.
  www.lcm-res.com
  lmrazek at lcm-res.com
  ph. 314-432-5886
  fx. 314-432-3304