Ah, I always enjoy another VB vs. C# discussion. As Woody Allen in one of his movies remembered his dad bickering with his mom, "OK! Have it it your way! The Atlantic Ocean is better than the Pacific!" As a programmer who cut his teeth on Fortran in the sixties, I have a number of languages under my belt, including the various dialects of C. My favorite was Pascal - now there was a readable language. I admit to having become a lazy man in my dotage. In these modern times I gravitate to VB.NET. My.Computer sure is handy! Yes, I run into incredulity and condescension from the young professional IT types (whippersnappers!), but the fact is the results are the same with either language and are not improved by curly brackets, no matter how many.

Ed Greenawald

Sorry, there was a typo in my last post. That query should have read

select userid,username,max(ct) as Over20
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username union Select top 1 userid,username,0 as ct from temptable as b where userid=1) group by userid,username

I have run the 2-step solution using a view on SQL Server and it works.

OK, you want to return a 0 if there are no scores over 20. That is trickier. This will return the count of Bob's scores over 20:

Select userid,username,count(*) as [count] from temptable where userid=1 and score>20 group by userid

This union query will return that same result and also a dummy record with a 0 count:

Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1

One would think then that using the MAX function in a sub-select would get your desired result:

select userid,username,max(ct) as
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1)
group by userid,username

But I find that in T-SQL for some reason it throws an error. However, if I first define a view based on that union, then this works:

select userid,username,max(ct) from view_c as Over20 group by userid, username

Try:

select userid,username,count(*) from temptable where userid=1 and score>20 group by userid

If you are still using the Access front-end, just pass the text as a parameter to the VBA FollowHyperlink function and it will open.

What you want to use is regular expressions. Plenty of examples on google, e.g. http://www.sqlteam.com/article/regular-expressions-in-t-sql[^]

Rather than beat up the poor questioner for asking, I'll just trust that he has his reasons and offer the suggestion that he use tools/database/compact and repair after deleting the records. This will cause the next autonumber to proceed in sequence. In general you should not do this for all the reasons others have supplied. But I have been known to do it myself when cleaning out test data before deployment.

Yer all young punks! I was coding in Algol and Fortran way back in 1964 when the internet was not yet a gleam in Al Gore's eye and "loosing" was still spelled "losing."

Like the face of Jesus in the tortilla, this "woman" is yet another manifestation of the brain's uncanny ability to interpret random signals as familiar forms. Newborns come already wired to recognize faces. Technical stock analysts think they see clear buy and sell signals in stock market noise. Uncle_Ed