Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: How Get The Sum of Multiple Entries by One Person in One Row

    Hi,
    I use access 2002 and what I want is simple in theory but providing to be difficult for me.

    Basically, I want to know how many bookings each Booking Staff enter between two date periods. I have sorted the Between dates but I am not getting the result that I want. For example, Mike has entered 20 Bookings, Michelle entered 15 Bookings on a particular date or between two dates, but when I run my report I get name of each Booking Staff several time with total of Bookings for Each Client ID which IS NOT what I want: What I want is a total of all bookings entered by each staff:

    For Example

    Mike Entered: 20
    Michelle Entered: 15

    Any help would be much appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Whats the SQL expression of the query you use?
    Have a nice day!

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi,
    Thank you for your response. Here is the SQL express of the query that I use:

    SELECT Count(Booking.CallTakenBy) AS [Total Of], Booking.CallTakenBy, Booking.DateOfEnquiryOnly, Booking.Language1ID
    FROM Booking
    GROUP BY Booking.CallTakenBy, Booking.DateOfEnquiryOnly, Booking.Language1ID
    HAVING (((Booking.DateOfEnquiryOnly)=[Enter Date]))
    ORDER BY Count(Booking.CallTakenBy), Booking.CallTakenBy DESC;
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would try:
    Code:
    SELECT Count(Booking.CallTakenBy) AS [Total Of], Booking.CallTakenBy, Booking.DateOfEnquiryOnly, Booking.Language1ID
    FROM Booking
    WHERE (((Booking.DateOfEnquiryOnly)=[Enter Date]))
    GROUP BY Booking.CallTakenBy, Booking.DateOfEnquiryOnly, Booking.Language1ID
    ORDER BY Count(Booking.CallTakenBy), Booking.CallTakenBy DESC;
    I don't see any reason for using a HAVING clause in this case.
    Have a nice day!

  5. #5
    Join Date
    May 2006
    Posts
    386
    Hi Sinndho,
    Thank you for your response. This code also did not work. It gives me the same information. It lists name of Booking staff several times based on Language1ID. Meaning, if one staff Booked 2 workers for Spanish, and 5 for French, the query result shows the workers name twice, once for Spanish with count of 2, and then for French with count of 5. It should only show the workers name once and the total number of entries, in this case it should show like this: Mike: 7
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Emal View Post
    Hi Sinndho,
    Thank you for your response. This code also did not work. It gives me the same information. It lists name of Booking staff several times based on Language1ID. Meaning, if one staff Booked 2 workers for Spanish, and 5 for French, the query result shows the workers name twice, once for Spanish with count of 2, and then for French with count of 5. It should only show the workers name once and the total number of entries, in this case it should show like this: Mike: 7
    This is normal because you use:
    Code:
    GROUP BY Booking.CallTakenBy, Booking.DateOfEnquiryOnly, Booking.Language1ID
    So, for every language (Booking.Language1ID) the query will create another group (row). You cannot expect to find the column [Language1ID] in the resulting data set and not GROUP BY on it. Otherwise and in your example, what should the column [Language1ID] contain for the row with 'Mike' in the [CallTakenBy] column: 'Spanish' or 'French' or what else? If you want to count all books whatever the language can be, drop Booking.Language1ID from the query.
    Have a nice day!

  7. #7
    Join Date
    May 2006
    Posts
    386
    Hi Sinndho,
    You are a life saver, thank you very much. It perfectly works fine now. I was just confused with the result but did not realise that it was also ordered by Language1ID. Thank you so much.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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