Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL 2000 Excel 2007

Options
  • 01-06-2007 2:07pm
    #1
    ROS123
    Registered Users Posts: 428 ✭✭


    Where to start, I have a need to build a query in excel, the data is stored in approx 6 small tables in SQL 2000. IS it possible to point excel (2003 / 2007)to more than one table in SQL 2000 and pull the infromation in to Excel to build reports and queries? Where do I start ?


Comments

  • Options
    #2
    Edser
    Registered Users Posts: 361 ✭✭Edser


    One way -

    you'll need an ODBC connection to the DB, In Excel, go to menu

    Data\'Import External data'\'New Database query' and select database.

    Choose tables and follow instructions/criteria

    Ed


  • Options
    #3
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    This is all relatively straightforward.
    Go to Data->Import External Data->New Database Query.
    Select <New Data Source> and click OK.
    Type in a name (any name, not important) and select SQL Server as the driver.
    Click Connect.
    Select your server and put in the username and password.
    Click OK. Click OK again.
    Select the tables you want to include in your query.
    Click Next.
    Select the filters you want or just pressing Next and choose the option to Edit the Query in Microsoft Query.

    Thats the basics of what you need. If you're still stuck let us know.


  • Options
    #4
    ROS123
    Registered Users Posts: 428 ✭✭ROS123


    Edser that was quick, I am just about running out the door now, catching a train but will have a go first thing on Tues Morn.... Thank you...


  • Options
    #5
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    seems I spent too long typing a reply :(


  • Options
    #6
    amen
    Registered Users Posts: 2,781 ✭✭✭amen


    write a Stored Procedure


  • Advertisement
  • Options
    #7
    ROS123
    Registered Users Posts: 428 ✭✭ROS123


    How do I do the next piece. I can see all the tables, but now I need to link descriptions from codes etc. i.e. in the main table I have some codes and I need to get descriptions for those codes from other tables in the database. Can I do this on the fly or do I need to import all the relevant tables into different worksheets and perform my queries from them.

    Sorry about Fri I was rushing for a train, didn't think I would get such quick response....


  • Options
    #8
    Kai
    Registered Users Posts: 604 ✭✭✭Kai


    You need to JOIN the tables together with an SQL query more than likely. Can you give us the details of the tables and we should be able to help you out.


  • Options
    #9
    ROS123
    Registered Users Posts: 428 ✭✭ROS123


    Kai, I have a dbase SQL 2000 There are data tables with names departments dates description etc, approx 6 tables in total, one being the main data tables containing info on dates and times, thie table is expanding all then time as records are added, there are 4 or 5 other tables with codes and descriptions that I want to be able to associate with the codes in the main data table. Ulltimately I want to be able to report be person, department, location, date range etc. potentialy in Excel for presentation purposes.


  • Options
    #10
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    http://www.w3schools.com/sql/default.asp is a good place to start when it comes to querying databases.


  • Options
    #11
    ROS123
    Registered Users Posts: 428 ✭✭ROS123


    Evil Phil, I see your advocating self help, and ordinarily I would concur, I was just under some pressure to get some reports out. I will certainly check out that fine line you provided.


  • Advertisement
  • Options
    #12
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    Yeah. You do that.


  • Options
    #13
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    You have pretty much been given all the info you need but I'll summarise if it helps. When you get to this step in the instructions I gave above

    Select the tables you want to include in your query.

    You need to add in all of your tables. If they fields in the main table and the field in description tables have the same name then it will automatically create the joins you need. If not you will need to drag a line from the field in the main table to the corresponding field in the destination table. If you are still stuck post your table structure and we'll take a look.


  • Options
    #14
    Edser
    Registered Users Posts: 361 ✭✭Edser


    When your're going through the query wizard choose all the fields from all the tables you need. Just before clicking Finish, choose the option - 'View or edit query in MS query'

    This should display the tables and links. i'm assuming knowledge of query building in MS access, etc.


  • Options
    #15
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    Edser wrote:
    When your're going through the query wizard choose all the fields from all the tables you need. Just before clicking Finish, choose the option - 'View or edit query in MS query'

    This should display the tables and links. i'm assuming knowledge of query building in MS access, etc.

    I could have sworn I already said that :)

    I dont think your assumption re the OPs knowledge is correct. If they knew how to build queries they would have been done already.


  • Options
    #16
    Edser
    Registered Users Posts: 361 ✭✭Edser


    Apologies Beano, you did indeed.

    Damn, shouldn't just scan thru posts! :confused:


Advertisement