Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: how to make this smart query?

    In the following table we record studentname, lecture_subject and presenting_date.
    table: studentlectures
    Code:
    ID  studentname          subject,     lecturedate
    1        john           politics,       1/1/12
    2        steve         politics,       16/1/12
    3        steve          economics,     21/1/12
    4        steve         politics,        22/1/12
    5        john            economics,     22/1/12
    6        john           politics,        25/1/12
    7        john           politics,        3/2/12
    We need a query to remove a lecturer who was presented less than a month on the same subject and by the same student. So we will get the following result:
    Code:
    ID  studentname          subject,          lecturedate
    1        john            politics,          1/1/12
    2        steve          politics,           16/1/12
    3        steve          economics,           21/1/12
    5        john            economics,          22/1/12
    7        john            politics,           3/2/12
    As you see records 4 and 6 are not shown because the same subject is presented by the same student in less than 30 days after the date when that subject was presented by the same student. For example record 6 is not shown because john had presented politics only 24 days before. Can we make this query?
    Thank you,
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    That is tricky. How do you end up with such a situation? Do you check at the time data is entered for just such a situation to prevent it? Or, is this just an exercise for a class project and is not real information?

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you say "a query to remove a lecturer " do you mean delete the row in the table or do you mean filter the data set ?
    Have a nice day!

  4. #4
    Join Date
    May 2012
    Posts
    132
    i just mean to filter it. in other words, those records are not shown in the final result , however they still exist in the original table.
    and this is a simplified example of a real situation i am facing. there are hundreds of records in a table that i need the repetitive ones not to be shown. the definition of "repetitive" in this case however is the combination of 2 fields of a record (student name and subject in our example).
    thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  5. #5
    Join Date
    Jan 2005
    Posts
    146
    Hi Ariansman. Interesting problem. I made a stab at it using a function to determine what to show or hide and implementing it in a query. Take a look and see if this satisfies your needs.
    Attached Files Attached Files

Posting Permissions

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