Query returning multiple of the same result (1 Viewer)

huttonst

New member
Local time
Today, 07:04
Joined
Feb 17, 2022
Messages
1
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

Super Moderator
Staff member
Local time
Today, 07:04
Joined
Feb 19, 2002
Messages
36,296
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

AWF VIP
Local time
Today, 04:04
Joined
Apr 13, 2010
Messages
1,173
In addition to Pat's comments add DISTINCT after SELECT to remove the duplicates.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:04
Joined
Oct 29, 2018
Messages
18,972
Hi. Welcome to AWF!

You might consider posting a sample db with test data.
 

Users who are viewing this thread

Top Bottom