(Excel 2007) Query Data in Database?

[Abel MacAdam]

Hi all,

I want to query data in a database in Excel. And I'm not exactly (actually
exactly not) sure how to get it done.

I opened the Data part of the buttons thingy, choose 'Get external data',
and next 'From other Sources'. I filled in the required information (like
database location, and username and password combination). In the next window
I selected a table, and the end result was that I imported the complete
table. And that is not what I was aiming on.

What I wanted to accomplish was to write a query using SQL. I have the query
available. What I now have to do is find the window where I can enter the
query. Do you know?

Abel

 

[joel]

The command text in a query is the SQL. You can add the sql as follows.

When you get to the last menu in the query (the one with the box FINISH)
select the VIEW or EDIT QUERY option. Then press finish

the query editor will appear. Look for the button with SQL. You can edit
the query here.

Another method if you already have aquery is to select some data in the
query. Then go to the menu Data - Import External Data and you will see the
Edit Query option enabled. Select this option and the find the SQL button.

 

[Abel MacAdam]

Hi Joel,

Thanks for your prompt response. I see a 'Finish' button in my 'Data
Connection Wizard' window, but no VIEW or EDIT QUERY option. We must be using
different transactions inside Excel.

In 2007 I choose Data: Get External Data -> From Other Sources -> From Data
Connection Wizard. The first window I got was the 'Data Connection Wizard',
where I choose 'Other/Advanced'. That opened the 'Data Link Properties'
windows, where I choose the 'Connection' tab. I entered my particulars for
the 'Use data source name', and 'User name' and 'Password'. In the 'Data
Connection Wizard' I choose a table. Next I had the opportunity to choose
where I wanted to put the data in the 'Import Data' window. In this window I
pressed the 'Properties' button, which brought me the 'Connection Properties'
window. In the 'Definition' tab I had the opportunity to enter the SQL query
in the 'Command text' text field. I choose the 'Command type' SQL. This
resulted in the message that I had an error in my SQL syntax at the last line
of my query (order by QUEUE, ADDRESS ASC). (QUEUE and ADDRESS are both tables
in the database I want to connect to. Maybe I'm almost there.

Is this the correct method of creating the query? Or do I have to try out
your second suggestion?

Abel

 

[joel]

I'm using excel 2003 so sometimes things are in different locations. You are
almost there. I like the Query Edit option in excel because you can enter
your SQL one item at a time to isloate problems.

Your ORDER BY statements should be columns in your results not a table in
Access. When you do a query you should be selecting the columns You want to
be put into excel. the sort has to be one of these columns. If you want to
sort by tables then you need to add the table name as one of the columns of
the results. You need to identify the new column. Then add this new ID into
the sort. You have the option of returning to excel this new column or use
it for the query.

I often use ACCESS to debug my database problems. You may want to look at
the help in access under SORT BY. You also may want to try your SQL statment
in Access before using it in Excel. The debug messages in Access are a
little better than excel.

There is a note in access :

If you specify a field containing Memo or OLE Object data in the ORDER BY
clause, an error occurs. The Microsoft Jet database engine does not sort on
fields of these types.

 

[Abel MacAdam]

Hi Joel,

Thanks for helping me until now.

I was slightly obscure in my wording. I should have said QUEUE and ADDRESS
are both headers of the Sheet in Excel, not tables in the database.

Maybe your Access note has merit. Have to look into it. Part of the select
statement is:
select DISTINCT CONCAT("https://anURL/Display.html?id=", CONV(T.id, 10, 10))
ADDRESS,
Q.name QUEUE,

Is the ADDRESS field to much for the Microsoft Jet database engine?

I will ponder your suggestions. Thanks

Abel