Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: more than 10 stars in current year or each year (was "Needs help in query")

    Hi:
    Can any one please tell me how do i write two queries

    1. to generate report of all those who have more then 10 stars Year to date
    2. to generate report of all those who have more then 10 stars in each year.

    Thanks.

    ID Date Name Given By Stars
    953 6/30/2005 Acito Lisa Virga, John
    940 6/30/2005 Acito Lisa Breuer, Dorothy 6
    1133 8/25/2005 Acito Lisa O'Hagan, Marian 2
    2692 8/30/2006 Acito Lisa Breuer, 5
    1251 8/31/2005 Acito Lisa Virga, John 5
    908 6/29/2005 Acito Lisa Breuer, Dorothy 2
    962 6/30/2005 Acito Lisa Breuer, Dorothy 5
    2742 9/30/2006 Acito Lisa Breuer, Dorothy 4
    2058 3/2/2006 Alfarone, Valerie Lopez, Charlene 5
    1835 12/30/2005 Alfarone, Valerie Lopez, Charlene 3
    2101 3/16/2006 Alfarone, Valerie Lopez, Charlene 5
    1984 2/13/2006 Alfarone, Valerie Breuer, Dorothy 5
    1749 12/5/2005 Alfarone, Valerie Lopez, Charlene 2
    1644 11/4/2005 Alfarone, Valerie Breuer, Dorothy 2
    1972 2/10/2006 Alfarone, Valerie Lopez, Charlene 10
    2107 3/17/2006 Alfarone, Valerie Lopez, Charlene 2
    2859 10/25/2006 Attanasio, Jennifer Breuer, Dorothy 5
    2942 10/30/2006 Attanasio, Jennifer Breuer, Dorothy 4
    2758 9/30/2006 Attanasio, Jennifer Breuer, Dorothy 4
    2743 9/30/2006 Attanasio, Jennifer Breuer, Dorothy 6
    547 3/11/2005 Barnes, Sylvia Cona, Patricia 5
    2890 10/27/2006 Barnes, Sylvia Attanasio, Jennifer 1
    273 11/24/2004 Barnes, Sylvia Breuer, Dorothy 0
    2892 10/27/2006 Barnes, Sylvia Webb, Pamela 1
    2888 10/27/2006 Barnes, Sylvia Lopez, Charlene 10
    1885 1/24/2006 Barnes, Sylvia Lopez, Charlene Sylvia 10
    1769 11/30/2005 Barnes, Sylvia Breuer, Dorothy 100% 5

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    effectively arent these the same?

    have a look at the SQL Aggregate functions, escpecially COUNT() or SUM() [depending on how you have designed your data], you will also need to use the GROUP BY clause

    have a look at the query designer, set the columns (fields) you want, add another column which counts something in your query.

    Id expect to the SQL to be something like:
    select ID, Date, Name, [Given By], count(Stars) as NoStars from <mytable> group by ID, and [optionally] Year(Date)

    incidentally I think you are potentially causing problems for yourself by defining column names with spaces, and Im not certain but i think you are in danger of using reserved words in SQL and/or VB. Also the colun names arent' all that descriptive... it probably doesnt matter on your schoolwork but if you plan on designing somehting for use elswhere it might

    Personally Id use somethimg like:-
    PupilID in place of ID,
    IssueDate in place of Date,
    PupilName
    GivenBy


    You need to do some work on your data to exclude null values, and data inconsistencies
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, you cannot get away with GROUP BY sloppiness in Access (or, as a matter of fact, in any other database besides MySQL)

    if you have select ID, Date, Name, [Given By] then you must also have group by ID, Date, Name, [Given By]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    healdem, you cannot get away with GROUP BY sloppiness in Access (or, as a matter of fact, in any other database besides MySQL)

    if you have select ID, Date, Name, [Given By] then you must also have group by ID, Date, Name, [Given By]

    ....curses smoked out yet again by Rudy, mebbe I ought to test answers before posting......
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •