Results 1 to 15 of 15
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    Creating new Query Help

    O.k., so I am trying to create a new query that says that If you have an attendance Multi violation warning type within a year on the BH Warnings table and another of any type of attendance warning (I.E. Verbal, Written, Final Written, and Termination) after the date of the multi Violation warning type and before a year has passed sense the Multi Violation warning that the query should append a termination Attendance Multi Violation to the BH warnings table. Right now the query that is in place is accomplishing the wrong task. Here is the table that I am going off. Any help getting a query going for this would be appreciated.



    DatabaseFile.zip

    Thank You
    Nick

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That sounds complicated. Pulling values from another record of the same table for comparison requires nested subquery or domain aggregate functions.

    Think I would build a VBA function for this situation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    OK ya Ill research into implementing vba thanks.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    I'm not sure about Access, but at first glance in SQL Server it can be done joining 2 instances of same table (left outer join, different aliases) and counting warnings in joined table. I have neither Access nor SQL Server available currently, so I can't test it jet.

  5. #5
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Here is what i wrote in my conditions. I just have a tough time starting the statement in vba

    1 condition if an ID has an attendance mv type violation within a year of todays date in the bh warnings table

    Iff true continue to 2nd condition and capture the trigger date of that record

    2 condition If the ID has an attendance violation type of any kind in bh warnings after the trigger date of the Attendance MV violation within a year

    IF true append a “termination” “Attendance MV” “todays date” to that ID number in the bh warnings table

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Here is the query, which lists all warnings of type "Attendance - M.V." within current year, and counts all additional warnings after that until today. Obviously you want those where NewViolationCnt > 0

    Code:
    SELECT w1.ID, w1.[Associate ID Number], COUNT(w2.Type) As NewViolationCnt
    FROM [B-H Warnings] w1 LEFT JOIN [B-H Warnings] w2 ON w2.[Associate ID Number]=w1.[Associate ID Number]
    WHERE w1.Type IN ("Attendance - M.V.") AND (w2.DateOfWarning>w1.DateOfWarning AND w2.DateOfWarning<Date()) AND (w1.DateOfWarning >= DateSerial(Year(Date()),1,1) AND w1.DateOfWarning < Date())
    GROUP BY w1.ID, w1.[Associate ID Number];

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Hope Arvi's approach with SQL using a self-join meets the requirements. I confess I am a little more comfortable with VBA than complex SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by ArviLaanemets View Post
    Here is the query, which lists all warnings of type "Attendance - M.V." within current year, and counts all additional warnings after that until today. Obviously you want those where NewViolationCnt > 0

    Code:
    SELECT w1.ID, w1.[Associate ID Number], COUNT(w2.Type) As NewViolationCnt
    FROM [B-H Warnings] w1 LEFT JOIN [B-H Warnings] w2 ON w2.[Associate ID Number]=w1.[Associate ID Number]
    WHERE w1.Type IN ("Attendance - M.V.") AND (w2.DateOfWarning>w1.DateOfWarning AND w2.DateOfWarning<Date()) AND (w1.DateOfWarning >= DateSerial(Year(Date()),1,1) AND w1.DateOfWarning < Date())
    GROUP BY w1.ID, w1.[Associate ID Number];
    Thanks Arvi for your help. I will try to implement the new code today.

    Thanks,
    Nick

  9. #9
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    OK so I made some modifications so that only the Associates ID is grouped together and the status of the violation is active. I am getting a very high reading though on one associate ID that I am not sure how it is getting that reading. I checked another employee and it seems to be good.

    Code:
    SELECT w1.[Associate ID Number], Count(w2.Type) AS NewViolationCnt
    FROM [B-H Warnings] AS w1 LEFT JOIN [B-H Warnings] AS w2 ON w1.[Associate ID Number] = w2.[Associate ID Number]
    WHERE (((w1.Type) In ("Attendance - M.V.")) AND ((w2.DateOfWarning)>[w1].[DateOfWarning] And (w2.DateOfWarning)<Date()) AND ((w1.DateOfWarning)>=DateSerial(Year(Date()),1,1) And (w1.DateOfWarning)<Date()))
    GROUP BY w1.[Associate ID Number], w1.Status
    HAVING (((w1.Status)="Active"));

  10. #10
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    I also need it to only count attendance type violations after getting the attendance mv.

  11. #11
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    I tried to add a criteria to the type count and got a data type mismatch.

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    Try
    Code:
    SELECT w1.[Associate ID Number], Count(w2.Type) AS NewViolationCnt
    FROM [B-H Warnings] AS w1 LEFT JOIN [B-H Warnings] AS w2 ON w1.[Associate ID Number] = w2.[Associate ID Number]
    WHERE (((w1.Type) In ("Attendance - M.V.")) AND ((w1.Status) In ("Active")) AND ((w2.DateOfWarning)>[w1].[DateOfWarning] And (w2.DateOfWarning)<Date()) AND ((w1.DateOfWarning)>=DateSerial(Year(Date()),1,1) And (w1.DateOfWarning)<Date()))
    GROUP BY w1.[Associate ID Number];
    
    NB. The query counts all following violations, warnings of type "Attendance - M.V." too - even ones with status not "Active". To exclude some following violations, you have to add according conditions into WHERE clause for w2.

    NB! NB! Scrap it! When throwing out w1.ID, you lost w1.DateOfWarning too. The join is not working anymore, because dates in w2 are compared with ALL DATES for associate in w1!

    The only way to achiwe what you wanted, is to create a saved query which list warnings of type "Attendance - M.V." you want to query for every associate, and use this query as w1 instead of [B-H Warnings].

  13. #13
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    I actually think I have a working solution but by different means. I changed the query from a count query to an if you find the wording attendance after you find attendance MV in the first table to give me that associates ID. So that I can append new language to the bh warnings. I believe this works out for me. I tried to manipulate the table and was able to successfully do so. Does this make sense to you?

    Code:
    SELECT w1.[Associate ID Number], w2.Type AS NewViolationCnt, w2.Level, w2.Status
    FROM [B-H Warnings] AS w1 LEFT JOIN [B-H Warnings] AS w2 ON w1.[Associate ID Number] = w2.[Associate ID Number]
    WHERE (((w1.Type) In ("Attendance - M.V.")) AND ((w1.Status) In ("Active")) AND ((w2.DateOfWarning)>[w1].[DateOfWarning] And (w2.DateOfWarning)<=Date()) AND ((w1.DateOfWarning)>=DateSerial(Year(Date()),1,1) And (w1.DateOfWarning)<Date()))
    GROUP BY w1.[Associate ID Number], w2.Type, w2.Level, w2.Status
    HAVING (((w2.Type)="Attendance") AND ((w2.Status) In ("Active")));

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    When it works, then it's OK. I don't have Access available at moment, so no way to check.

  15. #15
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Quote Originally Posted by ArviLaanemets View Post
    When it works, then it's OK. I don't have Access available at moment, so no way to check.
    Thank You for the help. I will come back and post when I have made it fully work within our system and mark it as solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating my First Query (not very well)
    By sorensjp in forum Queries
    Replies: 1
    Last Post: 10-22-2017, 08:22 PM
  2. Need Help Creating a Query
    By spyldbrat in forum Access
    Replies: 4
    Last Post: 09-24-2015, 08:18 AM
  3. Need help creating a query
    By MeginMurphy in forum Queries
    Replies: 3
    Last Post: 04-25-2014, 12:38 PM
  4. Replies: 3
    Last Post: 01-01-2013, 06:22 PM
  5. Creating a Query Need HELP
    By ampd in forum Queries
    Replies: 1
    Last Post: 02-16-2011, 12:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums