MS Query in Excel

[Paul]

I am new to SQL and using MS Query in Excel to extract
data from SQL Server.

The query retrives result from a number of tables and the
result is in the following format:

Card Number, Paid Date, Fee Paid, Total Fee Paid
1234, 23-11-2002, $20, $50
1234, 28-10-2003, $30, $50
1235, 10-10-2002, $20, $40
1235, 10-10-2003, $20, $40

Is there any way (like filtering / hiding column) to get
the following result:

Card Number, Total Fee Paid
1234, $50
1235, $40

Thanks
..

 

[CLR]

Hi Paul............

You should be able to set up your Query so that you only import the data you
want...........but assuming you're stuck with what you have, and assuming
it's in four columns A-D, then you could simply highlight the B and C above
those two columns and then right-click on them and either delete them or
hide them, leaving you with just columns A(card number) and D (Total fee
paid)............now, if all your data imports in column A alone, then you
can highlight column A and do Data > Text to columns, and use a comma as a
delimiter to separate the data into four columns, then proceed as
above.............

hth
Vaya con Dios,
Chuck, CABGx3

 

[Paul]

Thank you for your advice.

We are just linking tables and manipulate data in Excel.
Is it possible for us to write SQL in Excel Query. If
yes, can you give me some guidance ?

We cannot delete the two columns as they are linked fields
in the table.

 

[CLR]

Hi Paul..........

You're welcome, and I'm not sure about writing SQL in Query..........I just
use the Query menus to interrogate our MRP program to extract what I what to
Excel.

Another thing you might look at is Data > Filter > Autofilter, and then do
"Custom" like say on the Date column to separate only the entries for this
year............

Vaya con Dios,
Chuck, CABGx3

 

[DDM]

Paul, yes, you can write SQL statements in Microsoft Query. I don't know SQL
myself, but here's what the help topic from Microsoft Query says:

If you know SQL, you can use SQL to edit a query's SQL SELECT statement.

For more information about SQL, see the documentation for your database.
1.. Create a new database query. Do not return the results to Microsoft
Excel. Instead, click View data or edit query in Microsoft Query.
2.. Click View SQL button in Microsoft query. Microsoft Query displays the
SQL dialog box with the SQL SELECT statement that corresponds to the query.
3.. Type any changes you want to make into the SQL statement, and then
click OK. <
If you've already returned the data to Excel, you can click in it, click the
Edit Query button on the External Data toolbar, cancel the Query Wizard, and
click Yes when prompted to edit the query through Microsoft Query. Hope this
helps.

DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com

 

[onedaywhen]

We are just linking tables and manipulate data in Excel.
Is it possible for us to write SQL in Excel Query.

In the MS Query UI, choose: View, SQL or hit the toolbar button (hint:
it's got 'SQL' on it). Once in the SQL window, you could try the SQL
from here:

http://groups.google.com/[email protected]

--