[AccessD] Days of the Week Report

Thanks A.D. 

This was a big help! 


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

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Thursday, September 30, 2004 1:01 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com