If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
one to many query
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. |
#2
|
|||
|
|||
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 wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote: 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 wrote in message ... 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. |
#4
|
|||
|
|||
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 "cwr" wrote in message ... 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" wrote: 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 wrote in message ... 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. |
#6
|
|||
|
|||
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. wrote in message ... 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 -----Original Message----- 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 "cwr" wrote in message news:852C6303-B58A-4981-9763- ... 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" wrote: 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 wrote in message ... 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. . |
#7
|
|||
|
|||
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) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tina" wrote in message ... 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. wrote in message ... 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 -----Original Message----- 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 "cwr" wrote in message news:852C6303-B58A-4981-9763- ... 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" wrote: 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 wrote in message ... 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. . |
#8
|
|||
|
|||
thx Doug, i ought to get that for myself! g
"Douglas J. Steele" wrote in message ... 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) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tina" wrote in message ... 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. wrote in message ... 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 -----Original Message----- 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 "cwr" wrote in message news:852C6303-B58A-4981-9763- ... 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" wrote: 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 wrote in message ... 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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
query wizard error | Christen | General Discussion | 12 | August 13th, 2004 08:37 PM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |
SELECT function in Query | alexparks | Running & Setting Up Queries | 9 | July 5th, 2004 11:31 AM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |