Select Nesting? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Select Nesting?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-21 : 11:52:46

Can I count the number of occurances while selecting distinct recordset?

SELECT DISTINCT field.name
SELECT COUNT (*) field.name
WHER.....

Can anyone show me how to do this?

Mike B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 13:26:33

Is this allowed in Access:

SELECT Column1, COUNT(*)
FROM SomeTable
GROUP BY Column1

Tara

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-21 : 13:38:00

Thanks for the reply tara, but i tried the following and it does not render any resultset.

SELECT Component, COUNT (Component)
FROM tblStructuralSchedule
WHERE Project = '10002'
AND Component LIKE 'BC%'
AND Issued > 0
GROUP BY Component;

If I take out the AND Component, AND Issued lines, then I get results.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 13:40:05

Well do you have any rows that match the WHERE statement? I think not considering you aren't getting any rows back.

Run this to see if your WHERE statement is correct:

SELECT component
FROM tblStructuralSchedule
WHERE Project = '10002'
AND Component = 'BC%%'
AND Issued > 0

If you don't get any rows back, then your WHERE clause is wrong.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-21 : 15:06:13

Access uses * as a wildcard and not %.

- Jeff

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-22 : 08:03:33

Thanks guys, Tera was correct, something was wrong with the where clause. I retyped it and it worked fine.

Also, note to Jeff, I am using c++ with ADO. The % is used for the wild card because using the * causes problems of a different kind. Thank you for your response.

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources