Advanced Database Query using VB Script

[sudonim]

I am trying to do something a little bit more advanced than excel can
handle with the Import External Data -> New Database Query. With that
query, I can do something like this.

Author Title Units
--------------------------------------------------------
Smith, Bob Excel for Dummies 2
Smith, Bob Word for Dummies 8
Smith, Sarah Gone with the BSOD 18
Smith, Sarah Email for beginners 7



What I am trying to put together is something a little more flexible
such as the following:

Author Title Units
--------------------------------------------------------
Smith, Bob
: Excel for Dummies 2
: Word for Dummies 8
::Books by Bob Smith: 2
::Units in stock: 10
--------------------------------------------------------
Smith, Sarah
: Gone with the BSOD 18
: Email for beginners 7
::Books by Sarah Smith: 2
::Units in Stock: 25
--------------------------------------------------------


Im sure that to do this, I can use VB Script. Can anyone give some
advice on how to get started. With PHP, I would just do it with 2
loops. One select statement would call the Authors table and get
everything with Smith. Then it would loop through the smiths and get
all of the books for each.

Thanks for any help you can give! I am not adverse to buying a book if
that is your recommendation

P.S. I also need to set the formatting of each cell / row / column for
totals.

 

[Tom Ogilvy]

You can do most of what you want with a PivotTable Report under the data
menu.

 

[sudonim]

Tom,

Thanks for the response. PivotTables do allow me to do something like
this. However, their formatting is a little bit more limited. I really
need to have more control over formatting as we are creating reports to
the spec of our customers. For example, one limitation I found with
pivottables is they dont hold formatting well that you set yourself.
When you refresh the data, the formatting disappears. I am much more
comfortable with hardcoding the formatting.

Any help there?

Colin