Raymond,

Can you bend one or more of the pivot table examples at http://www.artfulsoftware.com/queries.php to your requirement?

PB

-----

Jacob, Raymond A Jr wrote:
Question: I frequently would like to summarize the results of my query in heiarchical layout also
known as a Pivot table.
Here is an example of what I would like output. NULL will be printed as a space 
when output.

sum of broken| source of  | qty      |reseller of |qty      |customer with |qty by
  bolts      |broken bolts|regional  |broken bolts|reseller |broken bolts  |customer
-----------------------------------------------------------------------------
 100         |  NULL      |NULL      |NULL        | NULL    |NULL          |NULL
(100/NULL)   |  US        | 75       |NULL        | NULL    |NULL          |NULL
(100/NULL)   | (US/NULL)  |(75/NULL) |ACME        | 35      |NULL          |NULL
(100/NULL)   | (US/NULL)  |(75/NULL) |(ACME/NULL) |(35/NULL)|Barney Rubble |20
(100/NULL)   |  US        | 75       |NULL        | NULL    |NULL          |NULL
(100/NULL)   | (US/NULL)  |(75/NULL) |ACME        | 35      |NULL          |NULL
(100/NULL)   | (US/NULL)  |(75/NULL) |(ACME/NULL) |(35/NULL)|Betty Rubble  |5
(100/NULL)   |  US        | 75       |NULL        | NULL    |NULL          |NULL
(100/NULL)   | (US/NULL)  |(75/NULL) |ABLE        | 25      |NULL          |NULL
(100/NULL)   | (US/NULL)  |(75/NULL) |(ABLE/NULL) |(25/NULL)|Sponge Bobble |20
(100/NULL)   |  MEXICO    | 15       |NULL        | NULL    |NULL          |NULL
(100/NULL)   | (MEX/NULL) |(15/NULL) |TIPPY       | 12      |NULL          |NULL
(100/NULL)   | (MEX/NULL) |(15/NULL) |(ACME/NULL) |(12/NULL)|Juan Pepe     |7


100  *  *  *   *   *             *
*   US  75 *   *   *             *
*   *   * ACME 35  *             *
*   *   *  *   *   Barney Ruble 20

Where * represents NULL or a Primary Key.

How does one build a pivot table?
from tables such as:

factory_parts table
 ::{
part no, 
plant, 
qty_manufactured
plant name
}
reseller_parts table
 ::{
part no
plant 
qty received
cost
reseller name
reseller id

}
customer_parts table
 ::{
reseller id
part no
plant
qty sold
qty recvd
customer id
customer name
}

Ooops now the light bulb comes on 
I would do:
   select factory_parts.plant name, 
          reseller_parts.reseller_name
          customer_parts.customer_name,
          customer_parts.qty_recvd
   from factory_parts,reseller_parts,customer_parts
   where customer_parts.part_no == 'broken_bolt' AND
         ( customer_parts.part_no == reseller_parts.part.no AND
           customer_parts.part_no == factory_parts.part.no )

Now the question becomes how does one construct the aggregate columns
representing the sum of bolts produced by the company,made at the plant,
shipped to the reseller and sold to the customer,
then join those aggregate columns? Any suggestions?

Thank you,
Raymond




  

No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005