Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    desnyder is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Oregon
    Posts
    19

    Find Duplicates

    I am NOT well versed in databases and for sure not SQL or VBA so if responding please keep it basic and dummy proof. Thanks
    I am trying to run a query to find only the [EmpID] shows up more than 3 times, I built a Find Duplicates query using the query Wizard (below is the SQL view of it) and it is not consistent and still shows some employees that only show up once.

    Code:
    SELECT [tble_Administrative Actions].Date, [tble_Administrative Actions].FirstName, [tble_Administrative Actions].LastName, [tble_Administrative Actions].Category, [tble_Administrative Actions].Offense, [tble_Administrative Actions].EmpID
    FROM [tble_Administrative Actions] INNER JOIN [HR Report Employee Master] ON [tble_Administrative Actions].EmpID = [HR Report Employee Master].EmployeeNumber
    WHERE ((([tble_Administrative Actions].Date)>=Now()-90) AND (([tble_Administrative Actions].EmpID) In (SELECT [EmpID] FROM [tble_Administrative Actions] As Tmp GROUP BY [EmpID] HAVING Count(*)>3 )) AND (([HR Report Employee Master].CurrentStatusDescription)="active"))
    ORDER BY [tble_Administrative Actions].EmpID;


  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is/are the condition(s) (plain English) for selecting these EmpIDs?
    May be helpful if you could post a zipped copy of your database.

  3. #3
    desnyder is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Oregon
    Posts
    19
    Orange - I know it may sound silly to you, but I don't know what you mean by "is/are the condition(s)", I have no training in database design and just make things up as I go along as you will see in my database.
    The basics of what I am trying to do -

    I have an employee table "HR report Employee Master" in which I down load from our scheduling system and import it into my database.
    I have a table that I built "tbl_Administrative Actions that hold all the counseling's, written warnings and so on.

    I am trying to build a report that will show the managers who has had more than 3 write ups in the last 90 days.

    I have tried to attached a zip copy of the database and it says its too large.
    Attached Thumbnails Attached Thumbnails Screenshot 2020-10-11 084057.png  

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This is the condition
    -managers who has had more than 3 write ups in the last 90 days.

    Also, DATE is a reserved word in Access. You may have to surround it [Date]..
    Similarly, fields with spaces in their names should be surrounded with [YourTableName].

    What result do you get from your query now?

    Did you do a Compact and Repair before trying to zip the database?
    What is the full design of the table tble_Administrative Actions?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think this has been made more complicated than it has to be because there's at least one missing table - tblEmployee. At the very least this will cause unnecessary repetition of data such as FName, LName. IMO it's important to note that an employee is one entity, the action is another. I don't understand the purpose of the actions table at this point, but that's probably because I can't see what else is in the master table.

    If you have embedded a lot of images or documents in a db table somewhere, you may not get this down to a size you can post here even if you compact repair and zip. BTW, you should copy the db and then do the c/r and zip on that. I advocate creating a backup before doing a c/r as in rare cases it can cause corruption. You don't want that to happen on your only or most up to date working version.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    desnyder is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Oregon
    Posts
    19

    Still not Working

    Thank you for all your doing to try and help. I made the adustments you mentioned and still get single entries in the results. See Attached
    Attached Thumbnails Attached Thumbnails Query Results.png   Query SQL View.png  

  7. #7
    desnyder is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Oregon
    Posts
    19
    UPDATE: I payed attention to your comment about the DATE field and to test it, I took the date criteria out of the query and just let it run all dates. This seemed to work, however, that does not help me as I need to filter it by date also. Thoughts?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I suspect your subquery is the issue, which is one of the things I alluded to that made the approach somewhat more complicated than it needs to be. Run that part by itself in a test query and see what you get. My thinking is that the GROUP BY is the cause for getting only one record per employee if that's what you're saying. It's what I'd expect when looking at your full sql.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micron re the subquery. I recommend that you work solely with the sql of the subquery to get those
    records in tble_Administrative Actions that have >=3 writeups in the last 90 days. Then once you have that working, use that result to get the details for the managers involved.

    How do you identify a writeup? A manager?
    Something along this setup (totally untested)

    Code:
    SELECT EmpID
    ,Count(writeupField) as NumWriteups
    FROM [tble_Administrative Actions]
    Group by Count(writeupField)
    Having Count(writeupField) >=3;
    Sample sql from one of my queries:
    Code:
    SELECT AnimalLocs.AnimalId
    , Count(AnimalLocs.SightingDate) AS CountOfSightingDate
    FROM AnimalLocs
    GROUP BY AnimalLocs.AnimalId
    HAVING (((Count(AnimalLocs.SightingDate))>=3));

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    What happens if you use Count(EmpID) ?

  11. #11
    desnyder is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Oregon
    Posts
    19
    Nice try but NO GO
    The only thing that makes it work is if I take out any criteria in the DATE field.

  12. #12
    desnyder is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Oregon
    Posts
    19
    Each record in the table is a "Writeup" for someone or an "administrative Action"
    The "Manager" has no part of the database, the manager is the one who runs the report.

    The way it is suppose to work is that supervisor will document administrative actions such as written warnings, councelings and such, each time they do it creates a record in the tble_Administrative Actions table.
    What I am trying to do is to create a report that the manager can run that will show any employoee that has obtain more than 3 enteries in a 90 day period.

    I hope I explained this correctly.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this query and let us know the result.
    SELECT EmpID
    ,Count(EmpID) as NumWriteups
    FROM [tble_Administrative Actions]
    Group by Count(EmpID)
    Having Count(EmpID) >=3;

  14. #14
    desnyder is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Oregon
    Posts
    19
    Not There Yet:

    Thank each of you for all your help - I know its hard working with a NOVICE like me, and you all have been great.
    I tried the above query and it gave me an error. I went ahead and created a new query with only the [DATE] and [EMPID], it is consistent that as long as I have a criteria in the [DATE] field it messes up. If I leave the [DATE] field empty, everything works fine.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    So try this one (3 writeups in last 90 days)
    SELECT EmpID
    ,Count(EmpID) as NumWriteups
    FROM [tble_Administrative Actions]
    WHERE [Date] Between Date and Date - 90
    Group by Count(EmpID)
    Having Count(EmpID) >=3;

    and provide a screen shot of the result please.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find duplicates query
    By raychow22 in forum Queries
    Replies: 4
    Last Post: 01-12-2018, 06:47 AM
  2. Find and delete duplicates
    By Khalil Handal in forum Access
    Replies: 6
    Last Post: 05-08-2017, 09:41 AM
  3. Replies: 3
    Last Post: 01-18-2016, 01:55 PM
  4. Query, find duplicates
    By gmaster in forum Queries
    Replies: 2
    Last Post: 05-27-2014, 06:58 AM
  5. Find Duplicates Query
    By mulefeathers in forum Queries
    Replies: 13
    Last Post: 04-22-2010, 05:39 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