sql - using complex LIKE

[avivit]

Hi.
2 problems regarding access sql or vb:
1.Using sql as follows, retrieves 1 empty record, though I have lots of records start with capital a :

SELECT name
FROM myTable
WHERE name like "A%";

Any idea?

2. I'd like to create a query or use vba or whatever in order to get a list in which each record holds the number of clients that there name start with each letter:

names start with A: 30
names start with B: 290
...
names start with z: 78

Any idea is welcome.
Thanks in advance

 

[grega]

In answer to part 2, you could try something like this:

select substring(name,1,1), count(*)
from myTable
group by substring(name,1,1)

This works on Sybase so you will need to refine it for Access.

Greg.

 

[tlbroadbent]

In Access try the following.

Select Left(Name,1,1) As FirstLetter, Count(Name) As RecCnt
From myTable
Group By Left(Name,1,1)

If you are using with Access, you will get better answers in an Access Forum rather than the ANSI SQL forum. Access is not ANSI Compliant.
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[tlbroadbent]

I forgot to post this code in response to your initial question. Your query failed because Access uses * for wildcard searches rather than %.

SELECT name
FROM myTable
WHERE name like "A*";

In addition, searches are not case sensitive so the following query will return the same result.

SELECT name
FROM myTable
WHERE name like "a*"; Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[avivit]

tlbroadbent, thanks.

1. I thought SQL is the same for all databases. I guess not all parts are. Now I see mt mistake in the LIKE thing.

2. Your 2nd sql statement did work for me (with the exception of removing the 2nd argument from "left&quot:

SELECT Left(name,1) AS firstLetter, Count(ejournals.Title) AS CountOfTitle
FROM myTable
GROUP BY Left(name,1);

3. i just saw "SQL" and ignored the "ANSI". What's ANSI anyway? (sorry for the ignorance)

Thank you very very much
My current problems are history thanks to you.

 

[avivit]

tlbroadbent, thanks.

1. I thought SQL is the same for all databases. I guess not all parts are. Now I see mt mistake in the LIKE thing.

2. Your 2nd sql statement did work for me (with the exception of removing the 2nd argument from "left&quot:

SELECT Left(name,1) AS firstLetter, Count(name) AS CountOfTitle
FROM myTable
GROUP BY Left(name,1);

3. i just saw "SQL" and ignored the "ANSI". What's ANSI anyway? (sorry for the ignorance)

Thank you very very much
My current problems are history thanks to you.

 

[tlbroadbent]

Sorry about the 2nd argument in the LEFT function. I must learn to be more careful when I copy and paste. ;-)

ANSI - American National Standards Institute is a private, non-profit organization that administers and coordinates the U.S. voluntary standardization and conformity assessment system. ANSI does not itself develop American National Standards. It facilitates development by establishing consensus among qualified groups. There is a group that works on the SQL standard.

Very few, if any, RDMS conform 100% to the ANSI standard.
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[avivit]

Thanks.

 

[ajsyx]

Question 1
With Access use
Select * from Supplier Where Name Like "a" & "*"

VBA use
SQL = "Select * from Supplier Where Name Like 'a' & '*'"

TransSQL or ANSI SQL
Select * From Supplier Where Name Like 'a%'



Question 2
Select Left(Name,1) as Customers, Count(Name) as Count
From Customers
Group By Left(Name,1)