Software

Ask something
Discussion Locked

Removing Duplicates In Access 2000 Query

0 helpful
10 replies

by borginva

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a query that has three tables:
PRODUCT, ORG, SERVICE.

Product.Org_ID and Service.Org_ID are tied into Org.Org_ID. Okay so far?

I have a property called SITE1. The ORG_ID is ORG558. Products are PRO1 and PRO2. Two expire dates under EXPIREDATE. Cool?

I add in fields ORG_ID and ORGNAME from the ORG table and PRODUCT from the PRODUCT table.

I get these results:
ORG558 SITE1 PRO1
ORG558 SITE1 PRO1
ORG558 SITE1 PRO2
ORG558 SITE1 PRO2

After putting in the Totals>Group By, Iget:
ORG558 SITE1 PRO1
ORG558 SITE1 PRO2

So far so good, right? That is what I want.
Well, once I add in EXPIREDATE from the SERVICE table, I get this:
ORG558 SITE1 PRO1 4/30/2001
ORG558 SITE1 PRO1 9/30/2002
ORG558 SITE1 PRO2 4/30/2001
ORG558 SITE1 PRO2 9/30/2002

As you can see, it will list each PRODUCT with each other's own maintenance figure, even with the Group BY active.
What I was looking for was:
ORG558 SITE1 PRO1 9/30/2002
ORG558 SITE1 PRO2 4/30/2001

So how can I get the above without the duplicates?

I hope I explained this correctly.

10 replies

by lord foul

Hi there! You need to use the DISTINCT clause in your query. This clause will select and display a single instance of the query result if there are two or more fields, matching your query.

See Ya!

by borginva

Looking at my data, each row is actually completely different and can not be DISINCTed. All the rows are actually unique.

by borginva

Hey, Ghost, I know little of Access. Can you tell me how to do that in the Access query?

by borginva

When I go into SQL view, this is what I have:

SELECT Org.Org_ID, Org.OrgName, Product.Product, Service.ExpireDate
FROM (Product INNER JOIN Org ON Product.Org_ID = Org.Org_ID) INNER JOIN Service ON Org.Org_ID = Service.Org_ID
GROUP BY Org.Org_ID, Org.OrgName, Product.Product, Service.ExpireDate
HAVING (((Org.Org_ID) Like "00558"))
ORDER BY Org.Org_ID;

What do I have to do with that DISTINCTROW command?

by borginva

I tried (by going into SQL view):

SELECT DISTINCTROW Org.Org_ID, Product.Product, Service.ExpireDate
FROM (Org INNER JOIN Service ON Org.Org_ID = Service.Org_ID) INNER JOIN Product ON Org.Org_ID = Product.Org_ID
WHERE (((Org.Org_ID) Like "00558"))
ORDER BY Org.Org_ID;

But I am still getting:

ORG558 PRO1 4/30/2001
ORG558 PRO1 9/30/2002
ORG558 PRO2 4/30/2001
ORG558 PRO2 9/30/2002

Where I only need:

ORG558 PRO1 9/30/2002
ORG558 PRO2 4/30/2001

Obviously I removed the site name to only work with 3 fields right now.

I can see the problem is trying to remove a duplicate PRODUCT for the ORG, but it has to keep the correct PRODUCT with the correct EXPIREDATE.

by borginva

After much pulling apart all this data, we found the problem is, and always has been, that there is nothing unique between the PRODUCT and SERVICE table. Without anyhting unique, it will not show the basic data correctly.

This discussion is closed to new replies.