I've got a SQL query in an Excel spreadsheet.

Unfortunately, three lines are time dependent and need to be updated on a
monthly basis.

The query follows:

/*************/

select Sub.AdviserDisplay,

sum(Sub.YTDExistingClients) as YTDExistingClients,

sum(Sub.YTDNewClients) as YTDNewClients,

sum(Sub.YTDAllClients) as YTDAllClients,

sum(Sub.PYTDExistingClients) as PYTDExistingClients,

sum(Sub.PYTDNewClients) as PYTDNewClients,

sum(Sub.PYTDAllClients) as PYTDAllClients

from

(

--Existing Clients

select AdviserDisplay,

count(distinct Assured1Id) as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200701 and 200702

and f.ExistingClient in (1,2)

group by AdviserDisplay



union all



--New Clients

select AdviserDisplay,

0 as YTDExistingClients,

count(distinct Assured1Id) as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200701 and 200702

and f.ExistingClient = 0

group by AdviserDisplay



union all



--All Clients

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

count(distinct Assured1Id) as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200701 and 200702

group by AdviserDisplay



union all



--Existing Clients Previous Year

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

count(distinct Assured1Id) as PYTDExistingClients,

0 as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200601 and 200602

and f.ExistingClient in (1,2)

group by AdviserDisplay



union all



--New Clients Previous Year

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

count(distinct Assured1Id) as PYTDNewClients,

0 as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200601 and 200602

and f.ExistingClient = 0

group by AdviserDisplay



union all



--All Clients Previous Year

select AdviserDisplay,

0 as YTDExistingClients,

0 as YTDNewClients,

0 as YTDAllClients,

0 as PYTDExistingClients,

0 as PYTDNewClients,

count(distinct Assured1Id) as PYTDAllClients

from fct_Policy f

inner join vdw_TimeCalendar t

on f.DateTransactionId = t.TimeId

inner join vdw_Adviser a

on f.AdviserId = a.AdviserId

where t.timemonthkey between 200601 and 200602

group by AdviserDisplay



) sub

group by Sub.AdviserDisplay

order by Sub.AdviserDisplay

/***************/

Date entries such as 200701 (Jan 2007) would need to be adjusted.

As there are a lot of these and it would be easy to get wrong, I'd like to
automate update of these.

Can I use VBA or a similar tool for this purpose.

Let's say the dates required are currently sat in cells somewhere in the
workbook - for the sake of the example, Master!A1, B1 etc.

Thanks for any guidance or suggestions you can provide.

Cheers,

Tom.

Given the complexity of the queries, if supported by the DB Engine, use a
stored procedure and pass the dates as parameters.
Using ADO, it would be pretty straight forward.

NickHK

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
newsD3E461A-9853-4D7C-B888-(E-Mail Removed)...
> I've got a SQL query in an Excel spreadsheet.
>
> Unfortunately, three lines are time dependent and need to be updated on a
> monthly basis.
>
> The query follows:
>
> /*************/
>
> select Sub.AdviserDisplay,
>
> sum(Sub.YTDExistingClients) as YTDExistingClients,
>
> sum(Sub.YTDNewClients) as YTDNewClients,
>
> sum(Sub.YTDAllClients) as YTDAllClients,

------------- SQL CUT ----------

> order by Sub.AdviserDisplay
>
> /***************/
>
> Date entries such as 200701 (Jan 2007) would need to be adjusted.
>
> As there are a lot of these and it would be easy to get wrong, I'd like to
> automate update of these.
>
> Can I use VBA or a similar tool for this purpose.
>
> Let's say the dates required are currently sat in cells somewhere in the
> workbook - for the sake of the example, Master!A1, B1 etc.
>
> Thanks for any guidance or suggestions you can provide.
>
> Cheers,
>
> Tom.

Nick,

Thanks so much for your answer.

I'm still learning this and don't know how to implement your solution.

How would I go about it? Do you know of any tips and tricks type pages
which concentrate on MS Query?

Cheers,

Tom.

"NickHK" wrote:

> Given the complexity of the queries, if supported by the DB Engine, use a
> stored procedure and pass the dates as parameters.
> Using ADO, it would be pretty straight forward.
>
> NickHK
>
> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> newsD3E461A-9853-4D7C-B888-(E-Mail Removed)...
> > I've got a SQL query in an Excel spreadsheet.
> >
> > Unfortunately, three lines are time dependent and need to be updated on a
> > monthly basis.
> >
> > The query follows:
> >
> > /*************/
> >
> > select Sub.AdviserDisplay,
> >
> > sum(Sub.YTDExistingClients) as YTDExistingClients,
> >
> > sum(Sub.YTDNewClients) as YTDNewClients,
> >
> > sum(Sub.YTDAllClients) as YTDAllClients,
>
> ------------- SQL CUT ----------
>
> > order by Sub.AdviserDisplay
> >
> > /***************/
> >
> > Date entries such as 200701 (Jan 2007) would need to be adjusted.
> >
> > As there are a lot of these and it would be easy to get wrong, I'd like to
> > automate update of these.
> >
> > Can I use VBA or a similar tool for this purpose.
> >
> > Let's say the dates required are currently sat in cells somewhere in the
> > workbook - for the sake of the example, Master!A1, B1 etc.
> >
> > Thanks for any guidance or suggestions you can provide.
> >
> > Cheers,
> >
> > Tom.
>
>
>

I'm not sure how MS Query will handle your complexity....

When you are in MS Query, show the SQL. Then for any value that you want to
be a parameter, replace the value with a "?", no quotes.
You will prompted for a value for each; enter any value at this stage.
Click return to Excel
Now, if you right-click on the query, you will see the Parameters... option
available. Set the values/ranges for each, as required.

NickHK

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
news:52E789D2-805E-44CE-8237-(E-Mail Removed)...
> Nick,
>
> Thanks so much for your answer.
>
> I'm still learning this and don't know how to implement your solution.
>
> How would I go about it? Do you know of any tips and tricks type pages
> which concentrate on MS Query?
>
> Cheers,
>
> Tom.
>
> "NickHK" wrote:
>
> > Given the complexity of the queries, if supported by the DB Engine, use
a
> > stored procedure and pass the dates as parameters.
> > Using ADO, it would be pretty straight forward.
> >
> > NickHK
> >
> > "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> > newsD3E461A-9853-4D7C-B888-(E-Mail Removed)...
> > > I've got a SQL query in an Excel spreadsheet.
> > >
> > > Unfortunately, three lines are time dependent and need to be updated
on a
> > > monthly basis.
> > >
> > > The query follows:
> > >
> > > /*************/
> > >
> > > select Sub.AdviserDisplay,
> > >
> > > sum(Sub.YTDExistingClients) as YTDExistingClients,
> > >
> > > sum(Sub.YTDNewClients) as YTDNewClients,
> > >
> > > sum(Sub.YTDAllClients) as YTDAllClients,
> >
> > ------------- SQL CUT ----------
> >
> > > order by Sub.AdviserDisplay
> > >
> > > /***************/
> > >
> > > Date entries such as 200701 (Jan 2007) would need to be adjusted.
> > >
> > > As there are a lot of these and it would be easy to get wrong, I'd
like to
> > > automate update of these.
> > >
> > > Can I use VBA or a similar tool for this purpose.
> > >
> > > Let's say the dates required are currently sat in cells somewhere in
the
> > > workbook - for the sake of the example, Master!A1, B1 etc.
> > >
> > > Thanks for any guidance or suggestions you can provide.
> > >
> > > Cheers,
> > >
> > > Tom.
> >
> >
> >

Thank you. That's a great first step.

If I could then get it to somehow pass through a value from a cell, it would
be perfect, but this really is a huge step forward.

Cheers,

Tom.

"NickHK" wrote:

> I'm not sure how MS Query will handle your complexity....
>
> When you are in MS Query, show the SQL. Then for any value that you want to
> be a parameter, replace the value with a "?", no quotes.
> You will prompted for a value for each; enter any value at this stage.
> Click return to Excel
> Now, if you right-click on the query, you will see the Parameters... option
> available. Set the values/ranges for each, as required.
>
> NickHK
>
> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> news:52E789D2-805E-44CE-8237-(E-Mail Removed)...
> > Nick,
> >
> > Thanks so much for your answer.
> >
> > I'm still learning this and don't know how to implement your solution.
> >
> > How would I go about it? Do you know of any tips and tricks type pages
> > which concentrate on MS Query?
> >
> > Cheers,
> >
> > Tom.
> >
> > "NickHK" wrote:
> >
> > > Given the complexity of the queries, if supported by the DB Engine, use
> a
> > > stored procedure and pass the dates as parameters.
> > > Using ADO, it would be pretty straight forward.
> > >
> > > NickHK
> > >
> > > "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> > > newsD3E461A-9853-4D7C-B888-(E-Mail Removed)...
> > > > I've got a SQL query in an Excel spreadsheet.
> > > >
> > > > Unfortunately, three lines are time dependent and need to be updated
> on a
> > > > monthly basis.
> > > >
> > > > The query follows:
> > > >
> > > > /*************/
> > > >
> > > > select Sub.AdviserDisplay,
> > > >
> > > > sum(Sub.YTDExistingClients) as YTDExistingClients,
> > > >
> > > > sum(Sub.YTDNewClients) as YTDNewClients,
> > > >
> > > > sum(Sub.YTDAllClients) as YTDAllClients,
> > >
> > > ------------- SQL CUT ----------
> > >
> > > > order by Sub.AdviserDisplay
> > > >
> > > > /***************/
> > > >
> > > > Date entries such as 200701 (Jan 2007) would need to be adjusted.
> > > >
> > > > As there are a lot of these and it would be easy to get wrong, I'd
> like to
> > > > automate update of these.
> > > >
> > > > Can I use VBA or a similar tool for this purpose.
> > > >
> > > > Let's say the dates required are currently sat in cells somewhere in
> the
> > > > workbook - for the sake of the example, Master!A1, B1 etc.
> > > >
> > > > Thanks for any guidance or suggestions you can provide.
> > > >
> > > > Cheers,
> > > >
> > > > Tom.
> > >
> > >
> > >
>
>
>

Nearly.

Get the message: "Parameters are not allowed in queries that can't be
displayed graphically."

Sorry.


"NickHK" wrote:

> I'm not sure how MS Query will handle your complexity....
>
> When you are in MS Query, show the SQL. Then for any value that you want to
> be a parameter, replace the value with a "?", no quotes.
> You will prompted for a value for each; enter any value at this stage.
> Click return to Excel
> Now, if you right-click on the query, you will see the Parameters... option
> available. Set the values/ranges for each, as required.
>
> NickHK
>
> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> news:52E789D2-805E-44CE-8237-(E-Mail Removed)...
> > Nick,
> >
> > Thanks so much for your answer.
> >
> > I'm still learning this and don't know how to implement your solution.
> >
> > How would I go about it? Do you know of any tips and tricks type pages
> > which concentrate on MS Query?
> >
> > Cheers,
> >
> > Tom.
> >
> > "NickHK" wrote:
> >
> > > Given the complexity of the queries, if supported by the DB Engine, use
> a
> > > stored procedure and pass the dates as parameters.
> > > Using ADO, it would be pretty straight forward.
> > >
> > > NickHK
> > >
> > > "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> > > newsD3E461A-9853-4D7C-B888-(E-Mail Removed)...
> > > > I've got a SQL query in an Excel spreadsheet.
> > > >
> > > > Unfortunately, three lines are time dependent and need to be updated
> on a
> > > > monthly basis.
> > > >
> > > > The query follows:
> > > >
> > > > /*************/
> > > >
> > > > select Sub.AdviserDisplay,
> > > >
> > > > sum(Sub.YTDExistingClients) as YTDExistingClients,
> > > >
> > > > sum(Sub.YTDNewClients) as YTDNewClients,
> > > >
> > > > sum(Sub.YTDAllClients) as YTDAllClients,
> > >
> > > ------------- SQL CUT ----------
> > >
> > > > order by Sub.AdviserDisplay
> > > >
> > > > /***************/
> > > >
> > > > Date entries such as 200701 (Jan 2007) would need to be adjusted.
> > > >
> > > > As there are a lot of these and it would be easy to get wrong, I'd
> like to
> > > > automate update of these.
> > > >
> > > > Can I use VBA or a similar tool for this purpose.
> > > >
> > > > Let's say the dates required are currently sat in cells somewhere in
> the
> > > > workbook - for the sake of the example, Master!A1, B1 etc.
> > > >
> > > > Thanks for any guidance or suggestions you can provide.
> > > >
> > > > Cheers,
> > > >
> > > > Tom.
> > >
> > >
> > >
>
>
>

Yes, well MS Query is relatively <simple>.

Time to bite the bullet and look at Stored Procedures
- depends on your DB

and/or ADO.
- http://support.microsoft.com/kb/257819

NickHK

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
news:159B14D5-3357-4AAC-AF14-(E-Mail Removed)...
> Nearly.
>
> Get the message: "Parameters are not allowed in queries that can't be
> displayed graphically."
>
> Sorry.
>
>
> "NickHK" wrote:
>
> > I'm not sure how MS Query will handle your complexity....
> >
> > When you are in MS Query, show the SQL. Then for any value that you want
to
> > be a parameter, replace the value with a "?", no quotes.
> > You will prompted for a value for each; enter any value at this stage.
> > Click return to Excel
> > Now, if you right-click on the query, you will see the Parameters...
option
> > available. Set the values/ranges for each, as required.
> >
> > NickHK
> >
> > "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> > news:52E789D2-805E-44CE-8237-(E-Mail Removed)...
> > > Nick,
> > >
> > > Thanks so much for your answer.
> > >
> > > I'm still learning this and don't know how to implement your solution.
> > >
> > > How would I go about it? Do you know of any tips and tricks type
pages
> > > which concentrate on MS Query?
> > >
> > > Cheers,
> > >
> > > Tom.
> > >
> > > "NickHK" wrote:
> > >
> > > > Given the complexity of the queries, if supported by the DB Engine,
use
> > a
> > > > stored procedure and pass the dates as parameters.
> > > > Using ADO, it would be pretty straight forward.
> > > >
> > > > NickHK
> > > >
> > > > "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> > > > newsD3E461A-9853-4D7C-B888-(E-Mail Removed)...
> > > > > I've got a SQL query in an Excel spreadsheet.
> > > > >
> > > > > Unfortunately, three lines are time dependent and need to be
updated
> > on a
> > > > > monthly basis.
> > > > >
> > > > > The query follows:
> > > > >
> > > > > /*************/
> > > > >
> > > > > select Sub.AdviserDisplay,
> > > > >
> > > > > sum(Sub.YTDExistingClients) as YTDExistingClients,
> > > > >
> > > > > sum(Sub.YTDNewClients) as YTDNewClients,
> > > > >
> > > > > sum(Sub.YTDAllClients) as YTDAllClients,
> > > >
> > > > ------------- SQL CUT ----------
> > > >
> > > > > order by Sub.AdviserDisplay
> > > > >
> > > > > /***************/
> > > > >
> > > > > Date entries such as 200701 (Jan 2007) would need to be adjusted.
> > > > >
> > > > > As there are a lot of these and it would be easy to get wrong, I'd
> > like to
> > > > > automate update of these.
> > > > >
> > > > > Can I use VBA or a similar tool for this purpose.
> > > > >
> > > > > Let's say the dates required are currently sat in cells somewhere
in
> > the
> > > > > workbook - for the sake of the example, Master!A1, B1 etc.
> > > > >
> > > > > Thanks for any guidance or suggestions you can provide.
> > > > >
> > > > > Cheers,
> > > > >
> > > > > Tom.
> > > >
> > > >
> > > >
> >
> >
> >

Managed it in VBQ and Query.

Thanks for all your help, Nick.

Tom.

"NickHK" wrote:

> Yes, well MS Query is relatively <simple>.
>
> Time to bite the bullet and look at Stored Procedures
> - depends on your DB
>
> and/or ADO.
> - http://support.microsoft.com/kb/257819
>
> NickHK
>
> "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> news:159B14D5-3357-4AAC-AF14-(E-Mail Removed)...
> > Nearly.
> >
> > Get the message: "Parameters are not allowed in queries that can't be
> > displayed graphically."
> >
> > Sorry.
> >
> >
> > "NickHK" wrote:
> >
> > > I'm not sure how MS Query will handle your complexity....
> > >
> > > When you are in MS Query, show the SQL. Then for any value that you want
> to
> > > be a parameter, replace the value with a "?", no quotes.
> > > You will prompted for a value for each; enter any value at this stage.
> > > Click return to Excel
> > > Now, if you right-click on the query, you will see the Parameters...
> option
> > > available. Set the values/ranges for each, as required.
> > >
> > > NickHK
> > >
> > > "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> > > news:52E789D2-805E-44CE-8237-(E-Mail Removed)...
> > > > Nick,
> > > >
> > > > Thanks so much for your answer.
> > > >
> > > > I'm still learning this and don't know how to implement your solution.
> > > >
> > > > How would I go about it? Do you know of any tips and tricks type
> pages
> > > > which concentrate on MS Query?
> > > >
> > > > Cheers,
> > > >
> > > > Tom.
> > > >
> > > > "NickHK" wrote:
> > > >
> > > > > Given the complexity of the queries, if supported by the DB Engine,
> use
> > > a
> > > > > stored procedure and pass the dates as parameters.
> > > > > Using ADO, it would be pretty straight forward.
> > > > >
> > > > > NickHK
> > > > >
> > > > > "mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
> > > > > newsD3E461A-9853-4D7C-B888-(E-Mail Removed)...
> > > > > > I've got a SQL query in an Excel spreadsheet.
> > > > > >
> > > > > > Unfortunately, three lines are time dependent and need to be
> updated
> > > on a
> > > > > > monthly basis.
> > > > > >
> > > > > > The query follows:
> > > > > >
> > > > > > /*************/
> > > > > >
> > > > > > select Sub.AdviserDisplay,
> > > > > >
> > > > > > sum(Sub.YTDExistingClients) as YTDExistingClients,
> > > > > >
> > > > > > sum(Sub.YTDNewClients) as YTDNewClients,
> > > > > >
> > > > > > sum(Sub.YTDAllClients) as YTDAllClients,
> > > > >
> > > > > ------------- SQL CUT ----------
> > > > >
> > > > > > order by Sub.AdviserDisplay
> > > > > >
> > > > > > /***************/
> > > > > >
> > > > > > Date entries such as 200701 (Jan 2007) would need to be adjusted.
> > > > > >
> > > > > > As there are a lot of these and it would be easy to get wrong, I'd
> > > like to
> > > > > > automate update of these.
> > > > > >
> > > > > > Can I use VBA or a similar tool for this purpose.
> > > > > >
> > > > > > Let's say the dates required are currently sat in cells somewhere
> in
> > > the
> > > > > > workbook - for the sake of the example, Master!A1, B1 etc.
> > > > > >
> > > > > > Thanks for any guidance or suggestions you can provide.
> > > > > >
> > > > > > Cheers,
> > > > > >
> > > > > > Tom.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>