Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: What's wrong with my SQL Query??

    I'm using the following code:

    SELECT teRManager, count(*) as ItemCount FROM Time_Entry GROUP BY teRManager

    and I seem to be getting numerous repeats when my goal is to get a total for each distinct instance of each item. It seems to work when I run it for other fields in the same table. Strange......

    Here is what I get:

    teRManager ItemCount
    Bruce Amman 59
    Jeff Mort 6275
    James Monroe 2556
    James Monroe;Jill Manners 38
    Jill Manners 4
    James Monroe;Jill Manners 17
    Jill Manners 1698
    Jill Manners;James Monroe 1
    Jill Manners 28
    Jill Manners;James Monroe 1
    Jill Manners 63
    Jill Manners;James Monroe 2
    Jill Manners 90
    Jill Manners;James Monroe 1

    Here is what I'm looking for:

    teRManager ItemCount
    Bruce Amman 59
    Jeff Mort 6275
    James Monroe 2556
    James Monroe;Jill Manners 55
    Jill Manners;James Monroe 5
    Jill Manners 1883

    (I'm using Access to pull the info from a Lotus Notes database)

    Thanks!

    Norm

  2. #2
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: What's wrong with my SQL Query??

    Your query looks correct. Do you have a primary or foreign key for the teRManager field? I ask, because is it possible that the values in the teRManager field are not all the same (though they look the same)? Perhaps some combinations of Jill Manners; James Monroe have extra spaces mixed into the text, or appended to the end of the names?

    For example (assuming '_' is a space), 'Jill_Manners" is not the same as "Jill_Manners_".

    Run a query of Select Distinct teRManager from Time_Entry and see if the name combinations show up more than once. If a combination shows up more than once, then there is some difference in how it was entered into the field.

  3. #3
    Join Date
    Jan 2004
    Posts
    100

    Re: What's wrong with my SQL Query??

    Originally posted by acg_ray
    Your query looks correct. Do you have a primary or foreign key for the teRManager field? I ask, because is it possible that the values in the teRManager field are not all the same (though they look the same)? Perhaps some combinations of Jill Manners; James Monroe have extra spaces mixed into the text, or appended to the end of the names?

    For example (assuming '_' is a space), 'Jill_Manners" is not the same as "Jill_Manners_".

    Run a query of Select Distinct teRManager from Time_Entry and see if the name combinations show up more than once. If a combination shows up more than once, then there is some difference in how it was entered into the field.
    I'm using a 4 field "combo" for my primary field. However, the teRManager is not one of them (nor is it a foreign key).

    I also considered that perhaps there was extra spacing or some hidden characters in one of the names. The only way I knew to test for it was copy the info and set them equal to each other in Excel and see if it came back "true" (which it did).

    Strangely, when I run your suggested query:

    Select Distinct teRManager from Time_Entry

    I don't get the repeated fields that I get during the first statement I used.

    Any ideas?

    Norm

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: What's wrong with my SQL Query??

    What version of Access are you using?

    The query looks right, so if your data is correct (which your SQL distinct query indicates), I'm at a loss for what else could be the issue.

  5. #5
    Join Date
    Jan 2004
    Posts
    100

    Re: What's wrong with my SQL Query??

    Originally posted by acg_ray
    What version of Access are you using?

    The query looks right, so if your data is correct (which your SQL distinct query indicates), I'm at a loss for what else could be the issue.
    I was using Access 2002.

    Thanks for the help.

  6. #6
    Join Date
    Jan 2004
    Location
    Somerset
    Posts
    5

    Re: What's wrong with my SQL Query??

    Originally posted by norm801
    I'm using the following code:

    SELECT teRManager, count(*) as ItemCount FROM Time_Entry GROUP BY teRManager

    and I seem to be getting numerous repeats when my goal is to get a total for each distinct instance of each item. It seems to work when I run it for other fields in the same table. Strange......

    Here is what I get:

    teRManager ItemCount
    Bruce Amman 59
    Jeff Mort 6275
    James Monroe 2556
    James Monroe;Jill Manners 38
    Jill Manners 4
    James Monroe;Jill Manners 17
    Jill Manners 1698
    Jill Manners;James Monroe 1
    Jill Manners 28
    Jill Manners;James Monroe 1
    Jill Manners 63
    Jill Manners;James Monroe 2
    Jill Manners 90
    Jill Manners;James Monroe 1

    Here is what I'm looking for:

    teRManager ItemCount
    Bruce Amman 59
    Jeff Mort 6275
    James Monroe 2556
    James Monroe;Jill Manners 55
    Jill Manners;James Monroe 5
    Jill Manners 1883

    (I'm using Access to pull the info from a Lotus Notes database)

    Thanks!

    Norm
    I might be off base...but dont you have to do an ORDER BY - so the data is grouped together - otherwise everytime it hits a change in teRManager is spits out the total count to date.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post the mdb - I would like to test it.

  8. #8
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: What's wrong with my SQL Query??

    In Access 2002, you should not have to use a ORDER BY statement in the grouping in order to get the proper count.

    The GROUP BY statement will order the data accordingly.

  9. #9
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: What's wrong with my SQL Query??

    Try to count a specific field as opposed to all the fields (*).

    For some reason I have had this work in the past.

  10. #10
    Join Date
    Jan 2004
    Posts
    100

    Re: What's wrong with my SQL Query??

    Originally posted by SimSoph
    Try to count a specific field as opposed to all the fields (*).

    For some reason I have had this work in the past.
    Great! Thanks for all the help....the problem has been solved.

    Norm

Posting Permissions

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