General discussion

Locked

Removing Duplicates In Access 2000 Query

By BorgInva ·
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.

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Removing Duplicates In Access 2000 Query

by DKlippert In reply to Removing Duplicates In Ac ...

Right click the relationship connector in the Query and change the union.

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

When I click "relationships" I get nothing but a blank gray screen.

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

When I click "relationships" I get nothing but a blank gray screen.

Collapse -

Removing Duplicates In Access 2000 Query

by Lord Foul In reply to Removing Duplicates In Ac ...

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!

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

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

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

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

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

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?

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

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.

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

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.

Collapse -

Removing Duplicates In Access 2000 Query

by BorgInva In reply to Removing Duplicates In Ac ...

This question was closed by the author

Back to Software Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums