Query is creating duplicates?

[bhipwell via AccessMonster.com]

This is weird.

I have a query (let's call it QryONE) that runs perfectly fine. However, if
I create a new query with the only source of data being QryONE, it is
duplicating some of the records.

I have no idea how this can happen since the query is make up of only on
other query (QryONE) that works perfectly fine on its own. With only one
query, there aren't any join issues either.

This is a result of a query that included QryONE as a data source which was
duplicating records. I began deleting the query away until I got down to
only the QryONE and STILL was getting duplicates.

Help!?

BH

 

[Jerry Whittle]

Please post the SQL statements for both queries here. Inquery design view go
to SQL view.

Next post some data from the first query and then some of the duplicates
from the second. If the data is sensitive, feel free to jumble it up some.

That way we can help you better.

 

[bhipwell via AccessMonster.com]

Query 1 that works:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeLastName, tblEmployee.
EmployeeFirstName, IIf([PlanClassType]="Flat",[PlanFlatAmount],(
[employeeannualincome]*[SalaryMaximum])) AS TypeCheck, IIf([typecheck]>
[benefitmaximum],[benefitmaximum],IIf([typecheck]<[benefitminimum],
[benefitminimum],([typecheck]*(1-[LIFE])))) AS LifeAmount, [lifeamount]/1000*
[PlanLifeRate]*(1-[ERContribution]) AS LifeEEMonthly, [lifeeemonthly]*
[payrollmultiplier] AS LifeEEPayroll, [lifeamount]/1000*[PlanLifeRate]*
[ERContribution] AS LifeERMonthly, [lifeermonthly]*[payrollmultiplier] AS
LifeERPayroll, tblClassPlan.Offered AS LIFEOff, tblPlans.PlanNumber,
tblEmployee.LifeElection, tblEmployer.EmployerID
FROM ((((tblEmployee INNER JOIN QryEmployeeAge ON tblEmployee.EmployeeID =
QryEmployeeAge.EmployeeID) INNER JOIN (tblPayroll INNER JOIN tblEmployer ON
tblPayroll.PayrollID = tblEmployer.PayrollID) ON tblEmployee.EmployerID =
tblEmployer.EmployerID) INNER JOIN (tblProducts INNER JOIN tblPlans ON
tblProducts.ProductsID = tblPlans.ProductsID) ON tblEmployee.LifeID =
tblPlans.PlansID) INNER JOIN tblClassPlan ON (tblEmployee.ClassID =
tblClassPlan.ClassID) AND (tblPlans.PlansID = tblClassPlan.PlansID)) INNER
JOIN QryCalculationsReductionsLife ON tblEmployee.EmployeeLastName =
QryCalculationsReductionsLife.EmployeeLastName;

Query 2 that is producing duplicates:

SELECT [EmployeeID], [EmployeeLastName], [EmployeeFirstName]
FROM QryCalculationsLife;

 

[Jerry Whittle]

What happens when you run the following:

SELECT tblEmployee.EmployeeID,
tblEmployee.EmployeeLastName,
tblEmployee.EmployeeFirstName
FROM ((((tblEmployee INNER JOIN QryEmployeeAge
ON tblEmployee.EmployeeID = QryEmployeeAge.EmployeeID)
INNER JOIN (tblPayroll INNER JOIN tblEmployer
ON tblPayroll.PayrollID = tblEmployer.PayrollID)
ON tblEmployee.EmployerID = tblEmployer.EmployerID)
INNER JOIN (tblProducts INNER JOIN tblPlans
ON tblProducts.ProductsID = tblPlans.ProductsID)
ON tblEmployee.LifeID =tblPlans.PlansID) INNER JOIN tblClassPlan
ON (tblEmployee.ClassID = tblClassPlan.ClassID)
AND (tblPlans.PlansID = tblClassPlan.PlansID))
INNER JOIN QryCalculationsReductionsLife
ON tblEmployee.EmployeeLastName =
QryCalculationsReductionsLife.EmployeeLastName
ORDER BY 1;

If you just want to get rid of the dupes, the following would work:

SELECT DISTINCT [EmployeeID],
[EmployeeLastName],
[EmployeeFirstName]
FROM QryCalculationsLife;