If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MS Query in Excel
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 .. |
#2
|
|||
|
|||
MS Query in Excel
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" wrote in message ... 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 . |
#3
|
|||
|
|||
MS Query in Excel
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. -----Original Message----- 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" wrote in message ... 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 . . |
#4
|
|||
|
|||
MS Query in Excel
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 "Paul" wrote in message ... 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. -----Original Message----- 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" wrote in message ... 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 . . |
#5
|
|||
|
|||
MS Query in Excel
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 "Paul" wrote in message ... 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. -----Original Message----- 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" wrote in message ... 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 . . |
#6
|
|||
|
|||
MS Query in Excel
"Paul" wrote ...
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 he http://groups.google.com/groups?selm...g .google.com -- |
Thread Tools | |
Display Modes | |
|
|