[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;
======================================

  ----- Original Message ----- 
  From: Andy Lacey 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, May 27, 2009 21:46
  Subject: [AccessD] Cross-tab (I think)


  Hi folks
  This should be simple but I can't see it.

  Have a query which gives a list of raw materials and suppliers, ie

  Mat1 SuppA
  Mat1 SuppB
  Mat1 SuppC
  Mat2 SuppA
  Mat2 SuppD

  and so on

  What I want is a query result for extract to Excel which transposes the
  suppliers into columns giving a single row per material. Thus:

  Mat1  SuppA   SuppB   SuppC
  Mat2  SuppA   SuppD
  Mat3  SupZZ
  etc

  We must have had this a zillion times but I can't get it to go.
  Heeeeeeeeeeeellppppp!
  --
  Andy