Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
can't change field name in query?
I'm using Microsoft Query to bring in some data. I have the query in SQL so i
can do some groupings and count values in one of the fields. My problem is I'm having difficulty renaming a field, so it comes back with no name at all. The SQL line is: SELECT [lots of fields], Count(StudentHistory.Status) AS CountStatus The count comes back properly, but the changed field name (i.e., CountStatus) doesn't appear. The field name is blank. How does this need to be written so the field name comes back as CountStatus? Thanks rachael |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
can't change field name in query?
Hi rachael,
You need to add a Group By clause to use the COUNT function in SQL Try something like this example (uses Northwind) SELECT Customers.Country, Customers.Region, Count(Customers.Region) AS CountStatus FROM master.dbo.Customers Customers Ed Ferrero http://www.edferrero.com "rachael" wrote in message ... I'm using Microsoft Query to bring in some data. I have the query in SQL so i can do some groupings and count values in one of the fields. My problem is I'm having difficulty renaming a field, so it comes back with no name at all. The SQL line is: SELECT [lots of fields], Count(StudentHistory.Status) AS CountStatus The count comes back properly, but the changed field name (i.e., CountStatus) doesn't appear. The field name is blank. How does this need to be written so the field name comes back as CountStatus? Thanks rachael |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
can't change field name in query?
Sorry - i don't think i was clear. The line i posted was just a fragment. The
full code works, and i do get a field back that shows the proper count. The problem is, the field has no name, even if i include "AS NewName". If i run the same SQL in Access, i get a field back with the proper count that has a field name of "NewName." Is this just a quirk in MS Query in Excel that i'm not getting back a field name? Or is there something else i need to do? Thanks rachael "Ed Ferrero" wrote: Hi rachael, You need to add a Group By clause to use the COUNT function in SQL Try something like this example (uses Northwind) SELECT Customers.Country, Customers.Region, Count(Customers.Region) AS CountStatus FROM master.dbo.Customers Customers Ed Ferrero http://www.edferrero.com "rachael" wrote in message ... I'm using Microsoft Query to bring in some data. I have the query in SQL so i can do some groupings and count values in one of the fields. My problem is I'm having difficulty renaming a field, so it comes back with no name at all. The SQL line is: SELECT [lots of fields], Count(StudentHistory.Status) AS CountStatus The count comes back properly, but the changed field name (i.e., CountStatus) doesn't appear. The field name is blank. How does this need to be written so the field name comes back as CountStatus? Thanks rachael |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
can't change field name in query?
Sorry rachael,
It is I who did not understand your question. Also, I see that I also left out the GROUP BY clause in my example. I can't reproduce your error - i.e. when I use Microsoft Query; - I connect to Northwind on SQL Server - I click the SQL button and enter SELECT Customers.Country, Customers.Region, Count(Customers.Region) AS 'CountStatus' FROM master.dbo.Customers Customers GROUP BY Customers.Country, Customers.Region - I click OK - Microsoft Query shows three columns Country, Region, and CountStatus - I return data to Microsoft Excel - See three columns in Excel with those same headings Some questions for you; What version of Excel do you use? What OS? When you say "the field has no name", do you mean that the data is returned in a column in Excel but has no heading Is the heading cell blank, or does it contain spaces or hidden charachters? (check in the formula bar) Ed Ferrero Sorry - i don't think i was clear. The line i posted was just a fragment. The full code works, and i do get a field back that shows the proper count. The problem is, the field has no name, even if i include "AS NewName". If i run the same SQL in Access, i get a field back with the proper count that has a field name of "NewName." Is this just a quirk in MS Query in Excel that i'm not getting back a field name? Or is there something else i need to do? Thanks rachael "Ed Ferrero" wrote: Hi rachael, You need to add a Group By clause to use the COUNT function in SQL Try something like this example (uses Northwind) SELECT Customers.Country, Customers.Region, Count(Customers.Region) AS CountStatus FROM master.dbo.Customers Customers Ed Ferrero http://www.edferrero.com "rachael" wrote in message ... I'm using Microsoft Query to bring in some data. I have the query in SQL so i can do some groupings and count values in one of the fields. My problem is I'm having difficulty renaming a field, so it comes back with no name at all. The SQL line is: SELECT [lots of fields], Count(StudentHistory.Status) AS CountStatus The count comes back properly, but the changed field name (i.e., CountStatus) doesn't appear. The field name is blank. How does this need to be written so the field name comes back as CountStatus? Thanks rachael |
#5
Posted to microsoft.public.excel.links
|
|||
|
|||
can't change field name in query?
Hi Ed -
I rebuilt the query entirely in MS Query (instead of copying a modified query built in Access), and it seems to work now. I don't know what changed, but i can't reproduce the problem either! But, to answer your questions below: Some questions for you; What version of Excel do you use? EXCEL 2003 What OS? WINDOWS XP PROF When you say "the field has no name", do you mean that the data is returned in a column in Excel but has no heading CORRECT Is the heading cell blank, or does it contain spaces or hidden charachters? (check in the formula bar) IT WAS BLANK Anyway, it seems to work now. Go figure. Thanks for your help! rachael "Ed Ferrero" wrote: Sorry rachael, It is I who did not understand your question. Also, I see that I also left out the GROUP BY clause in my example. I can't reproduce your error - i.e. when I use Microsoft Query; - I connect to Northwind on SQL Server - I click the SQL button and enter SELECT Customers.Country, Customers.Region, Count(Customers.Region) AS 'CountStatus' FROM master.dbo.Customers Customers GROUP BY Customers.Country, Customers.Region - I click OK - Microsoft Query shows three columns Country, Region, and CountStatus - I return data to Microsoft Excel - See three columns in Excel with those same headings Some questions for you; What version of Excel do you use? What OS? When you say "the field has no name", do you mean that the data is returned in a column in Excel but has no heading Is the heading cell blank, or does it contain spaces or hidden charachters? (check in the formula bar) Ed Ferrero Sorry - i don't think i was clear. The line i posted was just a fragment. The full code works, and i do get a field back that shows the proper count. The problem is, the field has no name, even if i include "AS NewName". If i run the same SQL in Access, i get a field back with the proper count that has a field name of "NewName." Is this just a quirk in MS Query in Excel that i'm not getting back a field name? Or is there something else i need to do? Thanks rachael "Ed Ferrero" wrote: Hi rachael, You need to add a Group By clause to use the COUNT function in SQL Try something like this example (uses Northwind) SELECT Customers.Country, Customers.Region, Count(Customers.Region) AS CountStatus FROM master.dbo.Customers Customers Ed Ferrero http://www.edferrero.com "rachael" wrote in message ... I'm using Microsoft Query to bring in some data. I have the query in SQL so i can do some groupings and count values in one of the fields. My problem is I'm having difficulty renaming a field, so it comes back with no name at all. The SQL line is: SELECT [lots of fields], Count(StudentHistory.Status) AS CountStatus The count comes back properly, but the changed field name (i.e., CountStatus) doesn't appear. The field name is blank. How does this need to be written so the field name comes back as CountStatus? Thanks rachael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the path of a Microsoft Query? | Excel Worksheet Functions | |||
Change button right before the formula field | Excel Discussion (Misc queries) | |||
HOW DO I CHANGE QUERY DATA TO A DATE RANGE? | Excel Worksheet Functions | |||
Find function | Excel Worksheet Functions | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |