[AccessD] Flattening the results of a query

You should use a cross tab query.
This is the way to go.
Two limits with crosstab.
No more than 256 (or something) products.
Only one value (product in you case) per query

This is pretty easy, sorry for not giving you a sample.
Create new query choose crosstab

Erwin

-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Stephen Bond
Verzonden: zaterdag 6 september 2003 3:46
Aan: accessd at databaseadvisors.com
Onderwerp: [AccessD] Flattening the results of a query


I have a query (Query1) that outputs as follows:

Cust1    Product1
Cust1    Product3
Cust2    Product9
Cust3    Product1
Cust3    Product2
Cust3    Product3
Cust3    Product4
Cust4    Product1
Cust4    Product2

And I want to flatten the results for output to a form so each line on
the screen looks like this:

Cust1    Product1, Product3
Cust2    Product9
Cust3    Product1, Product2, Product3, Product4
Cust4    Product1, Product2

I've done this by embedding a call to a VB function from the next query
in the nest, but even at this early stage of development I can tell the
speed (lack of) is going to be an issue.

Is there a clever way to flatten as shown above, using just the Jet
engine?

BTW, the 2 tables underlying Query1 are fully normalised and joined
one-to-many  -  I am asking for a quick method to denormalise.

Stephen Bond
Otatara, NZ
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com