Query returning multiple of the same result (1 Viewer)

[huttonst]

So for my database I am tracking certifications of employees. I have multiple tables, such as employees, employeeCerts, vendors, etc. In a form I have an input to search for a certain certification along with a check to search for employees that are active or not. I am searching for the correct certification name based on a dropdown menu of the ones from a certifications table. The active check for employees comes from the employees table. Before, there was an active employee check from the employeeCerts table, which worked right, however I changed it in order to make it possible for me to add a form to add to the records/tables of employees easier, and there would be multiple different certifications for each employee and having multiple checks when they were all the same didn't feel necessary to me.
Here is the query that I'm running when I click the search button on the form screen:
SELECT EmployeeCerts.Employee, Certs.Vendor, EmployeeCerts.Certification, EmployeeCerts.[Issue Date], EmployeeCerts.[Expiration Date], Employees.[Currently Active]
FROM Employees INNER JOIN (Certs INNER JOIN EmployeeCerts ON Certs.ID = EmployeeCerts.[Certification]) ON Employees.[Currently Active] = EmployeeCerts.[Currently Active]
WHERE (((EmployeeCerts.Certification)=Forms![Search Records]!cSearch) And ((Employees.[Currently Active])=Forms![Search Records]!activeCheck));
The problem is that I am returned with 50 rows of the same person/certification. The results of it are correct, returning what I would normally expect but the 50 copies of it is the issue.
I would appreciate any help that anyone could provide with this.

 

[Pat Hartman]

You are joining two tables on non-relationship fields. Each table has multiple instances of the join field values. Therefore, you are creating a Cartesian Product. You are multiplying tblA by tblB. So if tblA has 10 rows with value x and y and tblB has 5 rows with value x and y and you join on x = x and y=y, you will get 50 rows returned. i.e. 10 * 5 = 50

You either need to rethink your table design or summarize the data prior to joining.

 

[bastanu]

In addition to Pat's comments add DISTINCT after SELECT to remove the duplicates.

 

[theDBguy]

Hi. Welcome to AWF!

You might consider posting a sample db with test data.