Report pulling duplicate values with JOIN access

Question

I am pulling a report in access by writing a query in VBA and it has joining of tables. The problem is the report is now pulling duplicates values in the report.

ReCrt1 = "SELECT TeamDetails_v1.vName 
            FROM TeamDetails_v1 
            LEFT JOIN Attendance ON TeamDetails_v1.vName = Attendance.Name 
            WHERE ([Attendance!Team] In ('" & Mylist & "')) 
                AND ([Attendance!Dates]>Forms!AttendanceSummary!DTPicker0) 
                AND ([Attendance!Dates]<Forms!AttendanceSummary!DTPicker6) 
                AND ([TeamDetails_v1.vEmployment_Status]='Active')"
DoCmd.OpenReport "Monthly Report", acViewReport, , , , ReCrt1

The line breaks in SQL statement is not in code. I put it there for your ease.

I need the report to pull names from "TeamDetails_v1" table which has only unique name records and are 'active' in "vEmployment_Status". And their respective number of records(count formula is used in the Report) in "Attendance" table where dates should be in between the dates from date picker controls."Mylist" is a array containing certain teams filtered based on a list box in the form.

Here, instead of pulling single name records from TeamDetails_v1, the report pulls n number of same data if there is n number of dates. I have provided only a single textbox in report and its control source is given as "TeamDetails_v1.vName". Is there any issues with JOIN I used or is it the textbox field control source?

metadata metadata · Accepted Answer · 2017-12-06T16:36:45

Did you try putting the keyword "Distinct" after SELECT

 ReCrt1 = "SELECT DISTINCT TeamDetails_v1.vName FROM TeamDetails_v1 LEFT JOIN Attendance ON TeamDetails_v1.vName = Attendance.Name 
    WHERE ([Attendance!Team] In ('" & Mylist & "')) And ([Attendance!Dates]>Forms!AttendanceSummary!DTPicker0) And 
    ([Attendance!Dates]<Forms!AttendanceSummary!DTPicker6) And ([TeamDetails_v1.vEmployment_Status]='Active')"

        DoCmd.OpenReport "Monthly Report", acViewReport, , , , ReCrt1

Report pulling duplicate values with JOIN access

1 Answers