A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

one to many query



 
 
Thread Tools Display Modes
  #1  
Old August 25th, 2004, 10:17 PM
external usenet poster
 
Posts: n/a
Default 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  
Old August 26th, 2004, 02:38 AM
tina
external usenet poster
 
Posts: n/a
Default

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  
Old August 26th, 2004, 04:25 AM
cwr
external usenet poster
 
Posts: n/a
Default

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  
Old August 26th, 2004, 08:24 AM
tina
external usenet poster
 
Posts: n/a
Default

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.






  #5  
Old August 26th, 2004, 12:58 PM
external usenet poster
 
Posts: n/a
Default

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.





.

  #6  
Old August 26th, 2004, 04:22 PM
tina
external usenet poster
 
Posts: n/a
Default

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  
Old August 26th, 2004, 04:49 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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  
Old August 26th, 2004, 09:03 PM
tina
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 11:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.