[AccessD] Cross-tab (I think)

Wow AD
Can't wait to try this tomorrow. Will get back to you.

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

  ----- 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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com