[AccessD] Flattening the results of a query

Gustav Brock gustav at cactus.dk
Sat Sep 6 04:25:49 CDT 2003


Hi Stephen

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

I don't think there is except if the variety of products is very small
and you can live with SQL strings with hard coded product IDs.

However, you should be able to take advantage of Seek by (1) opening
the backend database directly and (2) creating the recordset as
Static.

Look up the archive (here we go again) for "Tree shaped reports" from
2002-02-06 14:22:06.

/gustav



More information about the AccessD mailing list