Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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 ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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 ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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 ... 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 ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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 ... 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 ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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 ... 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 ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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 ... 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 ... 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 ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Amend SQL Query with VBA
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 ... 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 ... 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 ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
amend a VB code please | Excel Discussion (Misc queries) | |||
Amend Formula | Excel Discussion (Misc queries) | |||
Amend year | Excel Worksheet Functions | |||
amend formula | Excel Discussion (Misc queries) | |||
Amend code | Excel Programming |