[AccessD] FW: Cross-tab (I think)

    Thanks for such generous compliments Andy! So nice of you. Your gracious post makes me feel on top of the world.

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Andy Lacey 
  To: Dba 
  Sent: Thursday, May 28, 2009 16:09
  Subject: [AccessD] FW: Cross-tab (I think)


  AD, this is just brilliant. Genius. I've just recreated this for my own
  tables and it gives exactly what I wanted. I am soooooooo impressed.

  To everyone else, store this in your kit bag. You may not need this now. You
  may not have picked up on what I was asking for. But the technique is a gem
  and so applicable to many situations.

  Thanks again AD :-))

  Andy


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
  Sent: 27 May 2009 19:56
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Cross-tab (I think)

  Andy,

  Crosstab query named Q_2 as given below, should get the results sought
  by you. It makes use of simple select query Q_1, also given below.

  T_Products is the table having text type fields named Product and
  Supplier respectively. Creation of sequential numbers (Rank) in Q_1 for each
  product, covering associated suppliers ensures that there are no redundant
  columns in the output provided by crosstab query Q_2.

  Note:
  In query Q_1, DCount() function has been used in lieu of a subquery as
  the latter does not suit subsequent use in crosstab query.

  Best wishes,
  A.D. Tejpal
  ------------

  Q_2 (Final query - Crosstab)
  ======================================
  TRANSFORM First(Q_1.Supplier) AS FirstOfSupplier
  SELECT Q_1.Product
  FROM Q_1
  GROUP BY Q_1.Product
  PIVOT Q_1.Rank;
  ======================================

  Q_1 (First stage query - Simple Select)
  ======================================
  SELECT T_Products.*, DCount("*","T_Products","Product = '" & [Product] & "'
  AND Supplier <= '" & [Supplier] & "'") AS Rank
  FROM T_Products;
  ======================================