Results 1 to 2 of 2
  1. #1
    lzook88 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    75

    Complicated Query Showing Duplicates

    Okay here we go trying to explain this......

    I have a table and a query.

    The table (tblEmployee) lists out the EMPLOYEES and all of their information and what they want to do

    The query (qryDeclined) lists out what DETAILS they declined to do.

    So i have a query that pulls up the Employee info and the Declined info together for a report. But if an Employee declines more than 1 thing the query is showing duplicates (1 for each decline, even if they are different details on the tbl)

    so here we go


    QryDetail1
    Employee_ID - tblEmployee - Autonumber
    EmployeeID - tblEmployee - short text
    EmployeeName - tblEmployee - short text
    EmployeStartDate - tblEmployee - date/time
    Detail1 - tblemployee - checkbox (Sorted to True)



    Declined: IIf([qrydeclined].[DeclineDetail]=96,"Yes","No") -96 is the detail number on the declined detail table

    DateDeclined: IIf([qrydeclined].[DeclineEmployee]=[EmployeeID] And [qrydeclined].[DeclineDetail]=96,[qrydeclined].[DeclineDate],"")

    DateEligible: IIf([qrydeclined].[DeclineEmployee]=[EmployeeID] And [qrydeclined].[DeclineDetail]=96,[qrydeclined].[Eligible],"")


    DeclineNts: IIf([qrydeclined].[DeclineEmployee]=[EmployeeID] And [qrydeclined].[DeclineDetail]=96,[qrydeclined].[DeclineNotes],"")

    CurrentlyOnDetail - tblEmployee - checkbox (Sorted to False) (Not visible on query)
    AdministrativelyIneligble - tblEmployee - checkbox (Sorted to False) (Not visible on query)
    ArchviedEmployee - tblEmployee - checkbox (Sorted to False) (Not visible on query)


    when i run the query and an employee has declined two DIFFERENT details it shows up like such

    Employee_ID EmployeeID EmployeeName Detail1 EmployeeStartDate YES Date Date Notes
    Employee_ID EmployeeID EmployeeName Detail1 EmployeeStartDate NO Blank Blank Blank

    I am trying to get rid of that one with the NO so it only shows up once on the report I run.. and then i can sort by the yes and no's


    any help or ideas are greatly appreciated.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if your table structure is one to many, you're going to get multiple records, you just have to narrow down your query more. For instance by grouping your reports by decline category so a person might appear in multiple categories. Or limit your report to a specific category. Alternately, if you wanted to find any person who had declined at least 1 category you would would use an aggregate query and use a min or max (depending on whether you're using a yes/no field or some other field) aggregation to create a single value for multiple rejections.

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

Similar Threads

  1. Duplicates showing up in query
    By BrockWade in forum Queries
    Replies: 7
    Last Post: 01-10-2014, 10:42 AM
  2. Union Query showing duplicates
    By scoe in forum Queries
    Replies: 6
    Last Post: 12-18-2013, 10:10 AM
  3. Replies: 2
    Last Post: 08-19-2013, 09:40 AM
  4. not showing duplicates
    By YStein142 in forum Reports
    Replies: 1
    Last Post: 06-28-2012, 05:52 PM
  5. Showing Duplicates in table
    By ericfatherree in forum Access
    Replies: 3
    Last Post: 02-06-2012, 07:58 AM

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