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

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

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

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

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