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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Annual count queries on a field relative to a value that varies DA | tobesus | General Discussion | 7 | May 10th, 2005 07:50 AM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |