Filter duplicates based off of one column (1 Viewer)

[gmatriix]

Hey Everyone.

I am shocked I have never came across this before.

I have a column like order number in a table and I just want to see the unique data based off that column.

I know to do district...but because of the data it still duplicates some columns.

How do I force it to only show unique values based off that column? without having to create a delete query??

 

[plog]

The answer is DISTINCT:

 SELECT DISTINCT YourFieldName FROM YourTableName

If that doesn't do it, please demonstrate your issue with data. Show us what data is in your table and what data you expect to end up with. Again, demonstrate it with data, not words.

 

[theDBguy]

Hi. If you're trying to view all the fields in your query's result, then you may need a subquery.

SELECT * FROM TableName WHERE 
FieldName In(SELECT DISTINCT FieldName FROM TableName)

 

[gmatriix]

The answer is DISTINCT:

 SELECT DISTINCT YourFieldName FROM YourTableName

If that doesn't do it, please demonstrate your issue with data. Show us what data is in your table and what data you expect to end up with. Again, demonstrate it with data, not words.

That is what I normally do and it fine but I think because of the joins, it still duplicating some data

In excel you can easily do this but I never had to do it in Access.

I select the "Main Number" because that is what I would want the filter to be based off of.

Is this possible in a query? or will I have to VBA something? Delete query?

 

[plog]

Please demonstrate what you want with data. I need 2 sets:

A. Starting data from your table. Include table and field name(s) and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with.

Again, data, not words, data. Show us what you expect based on what you have.

 

[gmatriix]

Hi. If you're trying to view all the fields in your query's result, then you may need a subquery.

SELECT * FROM TableName WHERE
FieldName In(SELECT DISTINCT FieldName FROM TableName)

I like this idea!
I will will try to use it another time. I tried it however, it did not work. Thanks tho

I just went ahead and created another table and set it for no duplicates and appended the data to get rid of them.

I test with the wizard to make sure.

I was just trying to do it in a query so I wouldn't have to create another table.....its ok....got the results I needed....

I do like that Idea tho

 

[theDBguy]

I like this idea!
I will will try to use it another time. I tried it however, it did not work. Thanks tho

I just went ahead and created another table and set it for no duplicates and appended the data to get rid of them.

I test with the wizard to make sure.

I was just trying to do it in a query so I wouldn't have to create another table.....its ok....got the results I needed....

I do like that Idea tho

Glad to hear you got it sorted out. Good luck with your project.