Advanced Database Query using VB Script

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.

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

--
Regards,
Tom Ogilvy

"sudonim" <[email protected]> wrote in message
news:[email protected]...
> 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,

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


Tom Ogilvy wrote:
> You can do most of what you want with a PivotTable Report under the
data
> menu.
>
> --
> Regards,
> Tom Ogilvy
>
> "sudonim" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >