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

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


________________________________________________
Message sent using UebiMiau 2.7.2