Counting entries in a database by first initial

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to get a simple count on entries for each letter of the alphabet. I pulled the customer number(count), last name (don't show) fields and I tried using the 'Left' expression but instead of a total over 100,000 I got a count of fewer than 2,000. I'm sure it is something simple I am doing wrong. Help!
 
It would help if you posted your query's SQL statement.

SELECT Left(Trim(LastName),1), Count(LastName) as CountThem
FROM YourTableName
GROUP BY Left(Trim(LastName),1)

If that fails or you don't understand, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
This is what I got in SQL. I entered the CountThem and evidently had a format problem. This statement give me an error #806, [ODBC Visual FoxPro Driver]SQL: Column ' ' is not found. What am I doing wrong?

SELECT Left(Trim([lastname]),1) AS Expr1, Count(Left(Trim([lastname]),1)) AS Expr2
FROM cust
GROUP BY Left(Trim([lastname]),1);
 
What are you doing wrong? Using FOXPRO???? Sorry, couldn't really resist.

Let's do a little troubleshooting: Try

SELECT Left(Trim([lastname]),1) AS Expr1, Count(Left(Trim([lastname]),1)) AS Expr2
FROM cust
WHERE Cust.[LastName] IS Not Null
GROUP BY Left(Trim([lastname]),1)

If that fails, lets try

SELECT Left(Trim([lastname]),1) AS Expr1
FROM cust
WHERE Cust.[LastName] IS Not Null

Do you get results? If not then try
SELECT Left([lastname],1) AS Expr1
FROM cust
WHERE Cust.[LastName] IS Not Null

If that fails, then try

SELECT LastName
FROM Cust
Where Cust.LastName is not null

If that fails, then something is wrong and I don't know what.

Let us know which ones fail and which ones work. I can't see anything to
explain the failure or error message. On the other hand, I have not had the
pleasure of working with FoxPro tables.
This is what I got in SQL. I entered the CountThem and evidently had a format problem. This statement give me an error #806, [ODBC Visual FoxPro Driver]SQL: Column ' ' is not found. What am I doing wrong?

SELECT Left(Trim([lastname]),1) AS Expr1, Count(Left(Trim([lastname]),1)) AS Expr2
FROM cust
GROUP BY Left(Trim([lastname]),1);
Click to expand...
 
Back
Top