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