Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL? Index? Excel?
Haven't done programming for a while-rusty now. Need help from any of you:
1) Trying to retrive data from SQL server to Excel by VBA. The SQL database is huge, which has over 50,000 data in it. But only 2 columns have index. For example, I want to output all the employee's name whose salary is below 10,000, and the group between 10,000 and 20,000, while the salary does not have index, but employee's name has index. As the database is huge, it takes forever to get the data. Anyone has idea to improve the speed? Or any alternative? 2) trying to run the macro and retrive some data into Excel sheet for selection. For example, I have 1000 employee use the bank HSBC, and another 2000 people use Whacovia. How can I get only one HSBC and one Whavocia into Excel cell for a selection? Thank you very much and appreciate your ideas. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL? Index? Excel?
1) Show us the SQL statement.
2) Use Data Validation. DataValidation, select Allow of List, and type HSBC and Whavocia in the edit box. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NiNi" wrote in message ... Haven't done programming for a while-rusty now. Need help from any of you: 1) Trying to retrive data from SQL server to Excel by VBA. The SQL database is huge, which has over 50,000 data in it. But only 2 columns have index. For example, I want to output all the employee's name whose salary is below 10,000, and the group between 10,000 and 20,000, while the salary does not have index, but employee's name has index. As the database is huge, it takes forever to get the data. Anyone has idea to improve the speed? Or any alternative? 2) trying to run the macro and retrive some data into Excel sheet for selection. For example, I have 1000 employee use the bank HSBC, and another 2000 people use Whacovia. How can I get only one HSBC and one Whavocia into Excel cell for a selection? Thank you very much and appreciate your ideas. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL? Index? Excel?
NiNi,
In SQL terms, 50,000 records is tiny, so this should be quick. I want to output all the employee's name whose salary is below 10,000 SELECT EmployeeName FROM my_table WHERE Salary <=10,000 the group between 10,000 and 20,000 SELECT EmployeeName FROM my_table WHERE Salary 10,000 AND Salary <=20,000 How can I get only one HSBC and one Whavocia into Excel cell for a selection? SELECT Top 1 EmployeeName FROM my_table WHERE Bank = 'Wachovia' SELECT Top 1 EmployeeName FROM my_table WHERE Bank = 'HSBC' Robin Hammond www.enhanceddatasystems.com "NiNi" wrote in message ... Haven't done programming for a while-rusty now. Need help from any of you: 1) Trying to retrive data from SQL server to Excel by VBA. The SQL database is huge, which has over 50,000 data in it. But only 2 columns have index. For example, I want to output all the employee's name whose salary is below 10,000, and the group between 10,000 and 20,000, while the salary does not have index, but employee's name has index. As the database is huge, it takes forever to get the data. Anyone has idea to improve the speed? Or any alternative? 2) trying to run the macro and retrive some data into Excel sheet for selection. For example, I have 1000 employee use the bank HSBC, and another 2000 people use Whacovia. How can I get only one HSBC and one Whavocia into Excel cell for a selection? Thank you very much and appreciate your ideas. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL? Index? Excel?
"Robin Hammond" wrote:
SELECT EmployeeName FROM my_table WHERE Salary <=10,000 AND Salary <= 20,000; Given that SQL is a description of the results sought, this is more descriptive and quite likely faster: SELECT EmployeeName FROM my_Table WHERE SALARY BETWEEN 10000 and 20000 How can I get only one HSBC and one Whavocia into Excel cell for a selection? SELECT Top 1 EmployeeName FROM my_table WHERE Bank = 'Wachovia' SELECT Top 1 EmployeeName FROM my_table WHERE Bank = 'HSBC' This gives two recordset objects; if the intention is to pick the distinct 'Bank' (there are two, Wachovia and HSBC), this will return the result is a single recordset & will cope with other distinct Bank names: SELECT Distinct(EmployeeName) FROM my_table GROUP BY Bank; |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Help Index tab | Setting up and Configuration of Excel | |||
SQL? Index? Excel? | Excel Discussion (Misc queries) | |||
Excel Addins - Index or key | Excel Programming | |||
Excel Addins - Index or key | Excel Programming | |||
Workbooks Index (Excel 97) | Excel Programming |