Access Query returning duplicates

I built the following SQl in Access 2007 and the query is returning duplicate rows.  I can not figure out why?


SELECT TblStatus.Status, [FName] & " " & [LName] AS nPatient, TblService.Type, [customers.First name] & " " & [customers.Last Name] AS ncustomer, Provider.Reviewer, Employees.[Last Name], Employees.[First Name], [Employees.First name] & " " & [Employees.Last Name] AS nEmployee, Provider.Reviewer, tblClaims.InvoiceID, tblClaims.CurrentYear, tblClaims.FileNo, tblClaims.Patient, tblClaims.Fname, tblClaims.Lname, tblClaims.DOI, tblClaims.[Client - Contact], tblClaims.[Claim#], tblClaims.[Date/Rec'd], tblClaims.[Date of IME], tblClaims.[Invoice #], tblClaims.[$Billed], tblClaims.[$Paid Doctor (cost)], tblClaims.[$Rec'd (income)], tblClaims.ServiceID, tblClaims.Specialty, tblClaims.State, tblClaims.Reviewer, tblClaims.[Gross Profit], tblClaims.Comments, tblClaims.[Complete Date], tblClaims.EmployeeID, tblClaims.StatusID, tblClaims.Cases, tblClaims.Description, tblClaims.Calls
FROM Provider, Employees INNER JOIN (Customers INNER JOIN (TblService INNER JOIN (TblStatus INNER JOIN tblClaims ON TblStatus.StatusID = tblClaims.StatusID) ON TblService.ServiceID = tblClaims.ServiceID) ON Customers.ID = tblClaims.[Client - Contact]) ON Employees.ID = tblClaims.EmployeeID
ORDER BY tblClaims.CurrentYear;
seamus99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
FROM Provider, Employees

The above syntax will create one record for each provider and each employee

Provider  Employee
1             John
2             Steve
3             Karen

If you don't want duplicates, then you need to join the Provider table to one of the other tables in the query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lee555J5Commented:
Do the "duplicate" rows actually exist in the tables? IOW, are the rows actually there and the SQL is working as intended, which means you need to delete the duplicate rows?

Sometimes DISTINCT and DISTINCTROW can eliminate duplicates. You can try them, but we'd need to see more of your db to know if they would work here.

SELECT DISTINCT TblStatus.Status, ...

and

SELECT DISTINCTROW TblStatus.Status, ...

Try them and see.

Lee
0
Dale FyeCommented:
The example data in my earlier post should have read

Provider  Employee
1             John
1             Steve
1             Karen
2             John
2             Steve
2             Karen

and I'm uncertain why you would need more than one instance of Provider.Review in your query?
0
lee555J5Commented:
Oh, good catch, fyed. I didn't see that comma buried in the SQL.

seamus99, fyed saw it; so if this fixes it, no points for me please. The comma says to create a Cartesian or Cross join, and this is rarely what you want. The clause

FROM Table A, Table B
basically combines every row from Table A with every row from Table B so you end up with
(# of rows in Table A) * (# of rows in Table B) = (# of rows in result set)
Tying your Provider table into the other tables with an actual JOIN statement should fix your issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.