How to count records by last name initial

[Jason]

I thought this would be pretty simple, but can't seem to get it. I have a
table with a few columns, but the only one I'm concerned about is the
LastName column.
I basically want to count how many customers for each alphabet letter.
So it would show:
A - 50
B - 25
C - 90
etc.
....

This is what I have:
SELECT Left([l_name],1) AS Expr2, Count(Left([l_name],1)) AS Expr1
FROM CUSTOMERS;

I clicked on the Totals button and selected to Count the Expr1 and to Group
By the Expr2. when I try to run, I get an error:
ODBC call failed. SQL column ' ' is not found (#806).
If I remove Expr2, the query runs but it only counts ALL of the last name
initials, it doesn't give me a breakdown per letter.

What am I doing wrong?

 

[Douglas J. Steele]

Is your data in an Access database, or in some other DBMS? If it's some
other DBMS, is this a pass-through query, or have you got a linked table?
(The ODBC in the error message makes me suspect the latter).

If it's an Access database or a linked table, the query needs to be

SELECT Left([l_name],1) AS Expr2, Count(*) AS Expr1
FROM CUSTOMERS
GROUP BY Left([I_name], 1)

If it's pass-through, you'll need to ensure that Left is a valid function in
that DBMS.

 

[Jason]

Doug, I'm working with a linked tabe in Access 2007 DB.
When I try to run your script I get an error:
"You tried to execute a query that does not include the specified expression
'Left([l_name],1)' as part of an aggregate function."

Is your data in an Access database, or in some other DBMS? If it's some
other DBMS, is this a pass-through query, or have you got a linked table?
(The ODBC in the error message makes me suspect the latter).

If it's an Access database or a linked table, the query needs to be

SELECT Left([l_name],1) AS Expr2, Count(*) AS Expr1
FROM CUSTOMERS
GROUP BY Left([I_name], 1)

If it's pass-through, you'll need to ensure that Left is a valid function
in that DBMS.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I thought this would be pretty simple, but can't seem to get it. I have a
table with a few columns, but the only one I'm concerned about is the
LastName column.
I basically want to count how many customers for each alphabet letter.
So it would show:
A - 50
B - 25
C - 90
etc.
...

This is what I have:
SELECT Left([l_name],1) AS Expr2, Count(Left([l_name],1)) AS Expr1
FROM CUSTOMERS;

I clicked on the Totals button and selected to Count the Expr1 and to
Group By the Expr2. when I try to run, I get an error:
ODBC call failed. SQL column ' ' is not found (#806).
If I remove Expr2, the query runs but it only counts ALL of the last name
initials, it doesn't give me a breakdown per letter.

What am I doing wrong?

 

[Douglas J. Steele]

Does the Left function work normally for you?

If not, your References collection could be messed up. While in a code
module, look in the ribbon for a link to References.

If any of the selected References (the checked ones at the top) have
MISSING: in front of them, unselect them. There's a possibility that doing
so may cause other things to stop working, so make sure you write down which
ones you deleted so that you can go back into the list and find them again
if need be.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, I'm working with a linked tabe in Access 2007 DB.
When I try to run your script I get an error:
"You tried to execute a query that does not include the specified
expression 'Left([l_name],1)' as part of an aggregate function."

Is your data in an Access database, or in some other DBMS? If it's some
other DBMS, is this a pass-through query, or have you got a linked table?
(The ODBC in the error message makes me suspect the latter).

If it's an Access database or a linked table, the query needs to be

SELECT Left([l_name],1) AS Expr2, Count(*) AS Expr1
FROM CUSTOMERS
GROUP BY Left([I_name], 1)

If it's pass-through, you'll need to ensure that Left is a valid function
in that DBMS.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I thought this would be pretty simple, but can't seem to get it. I have a
table with a few columns, but the only one I'm concerned about is the
LastName column.
I basically want to count how many customers for each alphabet letter.
So it would show:
A - 50
B - 25
C - 90
etc.
...

This is what I have:
SELECT Left([l_name],1) AS Expr2, Count(Left([l_name],1)) AS Expr1
FROM CUSTOMERS;

I clicked on the Totals button and selected to Count the Expr1 and to
Group By the Expr2. when I try to run, I get an error:
ODBC call failed. SQL column ' ' is not found (#806).
If I remove Expr2, the query runs but it only counts ALL of the last
name initials, it doesn't give me a breakdown per letter.

What am I doing wrong?

 

[Jason]

If I run just:
SELECT Left([l_name],1) AS Expr2
FROM patdatalink;

I get a result like:
H
W
B
J
ETC.

So that works, but for whatever reason, your code does not.

The only way that it seemed to work was to create a query of just the last
name initials, then turn it into a table. from there, create a query just
like the one you showed.

Maybe it's something to do with the ODBC connection???

Thanks for the help

Does the Left function work normally for you?

If not, your References collection could be messed up. While in a code
module, look in the ribbon for a link to References.

If any of the selected References (the checked ones at the top) have
MISSING: in front of them, unselect them. There's a possibility that doing
so may cause other things to stop working, so make sure you write down
which ones you deleted so that you can go back into the list and find them
again if need be.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, I'm working with a linked tabe in Access 2007 DB.
When I try to run your script I get an error:
"You tried to execute a query that does not include the specified
expression 'Left([l_name],1)' as part of an aggregate function."

Is your data in an Access database, or in some other DBMS? If it's some
other DBMS, is this a pass-through query, or have you got a linked
table? (The ODBC in the error message makes me suspect the latter).

If it's an Access database or a linked table, the query needs to be

SELECT Left([l_name],1) AS Expr2, Count(*) AS Expr1
FROM CUSTOMERS
GROUP BY Left([I_name], 1)

If it's pass-through, you'll need to ensure that Left is a valid
function in that DBMS.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I thought this would be pretty simple, but can't seem to get it. I have
a table with a few columns, but the only one I'm concerned about is the
LastName column.
I basically want to count how many customers for each alphabet letter.
So it would show:
A - 50
B - 25
C - 90
etc.
...

This is what I have:
SELECT Left([l_name],1) AS Expr2, Count(Left([l_name],1)) AS Expr1
FROM CUSTOMERS;

I clicked on the Totals button and selected to Count the Expr1 and to
Group By the Expr2. when I try to run, I get an error:
ODBC call failed. SQL column ' ' is not found (#806).
If I remove Expr2, the query runs but it only counts ALL of the last
name initials, it doesn't give me a breakdown per letter.

What am I doing wrong?

 

[Douglas J. Steele]

Hold on a moment. I just reread your error message. It implies that you
didn't include the GROUP BY clause that I included in my sample query:

SELECT Left([l_name],1) AS Expr2, Count(*) AS Expr1
FROM CUSTOMERS
GROUP BY Left([I_name], 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If I run just:
SELECT Left([l_name],1) AS Expr2
FROM patdatalink;

I get a result like:
H
W
B
J
ETC.

So that works, but for whatever reason, your code does not.

The only way that it seemed to work was to create a query of just the last
name initials, then turn it into a table. from there, create a query just
like the one you showed.

Maybe it's something to do with the ODBC connection???

Thanks for the help

Does the Left function work normally for you?

If not, your References collection could be messed up. While in a code
module, look in the ribbon for a link to References.

If any of the selected References (the checked ones at the top) have
MISSING: in front of them, unselect them. There's a possibility that
doing so may cause other things to stop working, so make sure you write
down which ones you deleted so that you can go back into the list and
find them again if need be.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, I'm working with a linked tabe in Access 2007 DB.
When I try to run your script I get an error:
"You tried to execute a query that does not include the specified
expression 'Left([l_name],1)' as part of an aggregate function."

Is your data in an Access database, or in some other DBMS? If it's some
other DBMS, is this a pass-through query, or have you got a linked
table? (The ODBC in the error message makes me suspect the latter).

If it's an Access database or a linked table, the query needs to be

SELECT Left([l_name],1) AS Expr2, Count(*) AS Expr1
FROM CUSTOMERS
GROUP BY Left([I_name], 1)

If it's pass-through, you'll need to ensure that Left is a valid
function in that DBMS.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I thought this would be pretty simple, but can't seem to get it. I have
a table with a few columns, but the only one I'm concerned about is the
LastName column.
I basically want to count how many customers for each alphabet letter.
So it would show:
A - 50
B - 25
C - 90
etc.
...

This is what I have:
SELECT Left([l_name],1) AS Expr2, Count(Left([l_name],1)) AS Expr1
FROM CUSTOMERS;

I clicked on the Totals button and selected to Count the Expr1 and to
Group By the Expr2. when I try to run, I get an error:
ODBC call failed. SQL column ' ' is not found (#806).
If I remove Expr2, the query runs but it only counts ALL of the last
name initials, it doesn't give me a breakdown per letter.

What am I doing wrong?