Results 1 to 6 of 6

Thread: Query Question

  1. #1
    Join Date
    Dec 2003
    Posts
    138

    Unanswered: Query Question

    I have a query that contains the fields

    [MedRevTrg1], [MedRevTrg2], [MedRevTrg3], [MedRevTrg4]

    These are med review training dates/updates. This must be updated quarterly.

    I'm trying to create a report based off of a query to determine in a specific month which staff need an update. I have fields that tell me when the update is due, however, I don't want to see all the fields, just the one that is due in that specific month.

    Please help!!!!

    Thanks!

    Dendalee

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What a yucky design...
    Code:
    SELECT updateDue
    FROM   myTable
    WHERE  Month(updateDue) = 10
    AND    Year(updateDue)  = 2007
    And because of the poor design this now becomes...
    Code:
    SELECT updateDue
          , updateDue2
    FROM   myTable
    WHERE  Year(updateDue)  = 2007
    AND    10 IN (Month(updateDue), Month(updateDue2), ... )
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2003
    Posts
    138
    I haven't worked with too many modules - am I programming this in the query, or creating a separate module?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's up to you.
    I suggest you create a new query, enter SQL view and paste the above in. Once you're happy with the results look at other methods if necessary.
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2003
    Posts
    138
    Per licensing requirements, I am forced to keep historical data. That is why I created the 4 fields. I have to keep a year's worth of training info.

    I may be able to determine what is due by the sql you gave me, however, I need to be able to keep a record of when that training was done and based on that date, determine when the next one is due.

    I have that part figured out, now what I am trying to do is to create a report based from that data.

    Is there an IIf statement that will show only the current training that is due?

  6. #6
    Join Date
    Dec 2003
    Posts
    138
    For those who might be interested, I ended up fixing this with 2 separate reports. The first report shows historical data regarding who received the training and on what dates.

    The second report simply states that training is due for employees here is the SQL for the query.

    SELECT Employees.LastName, Employees.FirstName, Employees.Title, Training.MedRevTrg1, DateAdd("m",3,Training!MedRevTrg4) AS Due1, Training.MedRevTrg2, DateAdd("m",3,Training!MedRevTrg1) AS Due2, Training.MedRevTrg3, DateAdd("m",3,Training!MedRevTrg2) AS Due3, Training.MedRevTrg4, DateAdd("m",3,Training!MedRevTrg3) AS Due4
    FROM Employees LEFT JOIN Training ON Employees.EmployeeID = Training.EmployeeID
    WHERE (((Employees.Title) Not Like "AA" And (Employees.Title) Not Like "Cook" And (Employees.Title) Not Like "HCOOK" And (Employees.Title) Not Like "HKeeping" And (Employees.Title) Not Like "Maint" And (Employees.Title) Not Like "OM" And (Employees.Title) Not Like "SM") AND ((Training.MedRevTrg1) Is Null) AND ((Employees.TerminationDate) Is Null)) OR (((DateAdd("m",3,[Training]![MedRevTrg4])) Between CDate([Forms]![View Reports]![Start Date]) And CDate([Forms]![View Reports]![End Date]))) OR (((DateAdd("m",3,[Training]![MedRevTrg1])) Between CDate([Forms]![View Reports]![Start Date]) And CDate([Forms]![View Reports]![End Date]))) OR (((DateAdd("m",3,[Training]![MedRevTrg2])) Between CDate([Forms]![View Reports]![Start Date]) And CDate([Forms]![View Reports]![End Date]))) OR (((DateAdd("m",3,[Training]![MedRevTrg3])) Between CDate([Forms]![View Reports]![Start Date]) And CDate([Forms]![View Reports]![End Date])))
    ORDER BY Employees.LastName, Employees.FirstName;


    It worked! Thanks for everyone's help.

Posting Permissions

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