Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2015
    Posts
    3

    Unanswered: Between 2 dates and counted on one field

    Hi

    I have 3 tables. 1 employee, 1 course, and 1 employee courses. I would like to run a report that show what courses were attended by the number of employees. For example of I have 50 courses that an organization runs. 5 do one course in month and another do 6 in a month. I want to show which courses had what number of attendees with a between date range - Between [please enter from date] and [please enter to date]. For some reason that i can not understand, as soon as i put this query (Between) in the dateattended field it stops counting and gives me every course indivially with every employee ID. Lost!! Please help, I have wragling with this for a few hours and just cant waor it out.

    thanks


    leo

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the SQL expression you are using now?
    Have a nice day!

  3. #3
    Join Date
    Mar 2015
    Posts
    3

    Between 2 dates and counted on one field

    Quote Originally Posted by Sinndho View Post
    What's the SQL expression you are using now?
    Hi

    I am not using SQL, as I am not good at it, at all, I was hoping to just add a "Between" date criteria in the query for the user to search between dates and then it returns the courses held in that date range with the number of attendees. Attached is a picture of my query.

    thanks this is driving me mad!
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  4. #4
    Join Date
    Mar 2015
    Posts
    3

    Between 2 dates and counted on one field

    Quote Originally Posted by Sinndho View Post
    What's the SQL expression you are using now?
    Hi

    I am not using SQL, as I am not good at it, at all, I was hoping to just add a "Between" date criteria in the query for the user to search between dates and then it returns the courses held in that date range with the number of attendees. Attached is a picture of my query.

    thanks this is driving me mad!
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry what is the point of attaching a thumbnail of a table which doenst' have the datetime columns in......

    most of the active contributors here dont' use the query designer, they tend to write the SQL directly. but there is nothign stopping uyou posting the SQL here. open the query designer, switch to SQL view and cut and paste the SQL here.

    howver I would caution you against using runtime parameters in a query (those where your users enter valeus when the query runs... why?
    1) you cannot validate the user input, so it could well be rubbish
    2) the between construct expects a specific format
    Code:
    mycolumn BETWEEN lowestvalue AND highestvalue
    3) if users have to enter the same dates in multiple queries its a pain to do, and you cannot guarantee that the same values will be used
    4) unless you live int he US or US centric date world you are asking for for trouble in gettign users to enter dates. Access/JET usually handles dates well enough but strictly ispeaking dates should be ISO (yyyy/mm/dd) or US format (mm/dd/yyyy), and date literals should be delimited by #

    so for this sort of things I'd strongly recommend that you use a form to make certain that values are 'sane' before submitting them either use a datecontrol or some form of list box to ensure that users can only specify sensible values. you can enforce what ever validations in that form. you cna use the same valeus in subsequent forms/reports
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2010
    Posts
    10
    [QUOTE=leorichard;6631802]Hi

    Hi

    Sorry here it the whole query.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  7. #7
    Join Date
    Jan 2010
    Posts
    10
    SELECT Count(tblcoursedetail.coursename) AS CountOfcoursename, tblcoursedetail.CourseID, tblcoursedetail.coursename
    FROM tblcoursedetail INNER JOIN tblempcourses ON tblcoursedetail.CourseID = tblempcourses.CourseID
    GROUP BY tblcoursedetail.CourseID, tblcoursedetail.coursename;

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT Count(tblcoursedetail.coursename) AS CountOfcoursename, tblcoursedetail.CourseID, tblcoursedetail.coursename
    FROM tblcoursedetail INNER JOIN tblempcourses ON tblcoursedetail.CourseID = tblempcourses.CourseID
    GROUP BY tblcoursedetail.CourseID, tblcoursedetail.coursename
    Having mydatecolumn between [Enter lower date limit as mm/dd/yyyy] and [Enter upper date limit as mm/dd/yyyy];
    ..not to sure offhand if its HAVING or a WHERE clause (essentially its the same thing although in SQL terms it isn't!)

    Code:
    SELECT Count(tblcoursedetail.coursename) AS CountOfcoursename, tblcoursedetail.CourseID, tblcoursedetail.coursename
    FROM tblcoursedetail INNER JOIN tblempcourses ON tblcoursedetail.CourseID = tblempcourses.CourseID
    GROUP BY tblcoursedetail.CourseID, tblcoursedetail.coursename
    WHERE mydatecolumn between [Enter lower date limit as mm/dd/yyyy] and [Enter upper date limit as mm/dd/yyyy];
    change the mydatecolumn to which ever of your date columns you are querying
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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