one to many query

[Guest]

I have spent hours trying to select a count.
I have a table with 3 of the ten columns containing a
student id, a year and a school id. I am trying to
create a report that will list each school, year, and
count of student ids.

example: there could be 10 records with school ID = 1,
year = 2003 and studenid = 123....what is different with
each of these records is a course id, dates, etc.
I am trying to set a query up using new query with sql
specific to code select statement .....i would need
distinct on all 3 fields with a count of distinct student
IDs..if am trying nesting in selects.

SELECT count(studentID), schoolID, year
FROM
(select distinct (studentID), schoolID, year from
( select studentID, distinct (schoolID), year from
(select studentID, schoolID, distinct (year) from
tblStudentCourseAndGrades));


Any help appreciated.

 

[tina]

try this, substituting correct table and field names, of course:

SELECT School, SchYear, Count(Student) AS CountOfStudent
FROM (SELECT DISTINCT School, SchYear, Student
FROM Table14)
GROUP BY School, SchYear

btw, i hope you don't have a table field actually named "Year". that might
cause problems.

hth

 

[Guest]

Thank you ...what you gave me worked. Actually, I do have a field named
Year...so far has not caused problems, but think I might change it in case.

 

[tina]

you're welcome, glad it worked for you.
if you search for "reserved words" in Access Help, you'll find the SQL
Reserved Words topic, which has a list of "all words reserved by the
Microsoft Jet database engine for use in SQL statements." Year is on that
list, as are Month and Day, and many others. also, be careful not to give
fields the same name as object properties, such as "Name".
an easy way to avoid using a reserved word or object property as a name, is
to put a prefix on anything you name. for instance, i put 1 to 3 letters
from a table's name as the prefix on all fields in that table, example:

tblEmployees
eID
eFirst
eLast
eDOB
etc, etc, etc.

hth

 

[Guest]

That is excellent info.
One more question. I have several books on SQL, but for
MySQL, DB2 etc. Even a SQL for Dummies book...I went thru
all of them and I still would not have come up with the
statement you gave me for the count I wanted.

What book did you learn this from or is there a book you
recommend. I will look for some additional books at the
bookstore but sometimes people come up with
recommendations that are good.

Thanks

 

[tina]

hmmm, i'm a poor one to ask. SQL is not my strong point. actually, i learned
about nested SQL statements by reading posts here in the newsgroups (i've
learned a lot of things that way), and have never used any in my own work
yet. in fact, when i need a SQL statement in VBA, i usually build the query
in the QBE grid first, then go to the SQL pane and copy it. <g>
i'm going to post this reply as "unresolved" and perhaps somebody else will
offer good suggestions. suggest you do *not* post a reply, else folks may
ignore the thread. if you don't get a response in a day or two, you can
repost the question to a new thread. good luck.

 

[Douglas J. Steele]

See whether you can get a copy of SQL Queries for Mere Mortals, by Michael
J. Hernandez and John L. Viescas (ISBN No. 0-201-43336-2)

Check http://www.viescas.com/Info/books.htm for more suggestions (and links
to where you can get them)

 

[tina]

thx Doug, i ought to get that for myself! <g>

See whether you can get a copy of SQL Queries for Mere Mortals, by Michael
J. Hernandez and John L. Viescas (ISBN No. 0-201-43336-2)

Check http://www.viescas.com/Info/books.htm for more suggestions (and links
to where you can get them)