How to Hide Duplicates in a Query for Just One Field

[maytime]

I have a query where I need to hide all duplicates for just one field. There are a few duplicates in this field, but the duplicate records contain different data in the other fields in the query, so the setting the "Unique Values" property to "Yes" doesn't work.

Is there some SQL code or some other way to hide these duplicate records based on just one field column of the query? I would be fine with Access hiding whichever duplicate field's record it found second.

All I can find online is how to hide true duplicate records (all data across all fields is the same) or how to create duplicate finder queries to remove records. Neither options work in my case.

Thanks!

 

[plog]

You can't just look at one field between two records and determine they are duplicates, you have to look at the entire record. And since other fields have differing values, they are not duplicates.

There may be a way to get the data like you want, but we would need some sample data. Please show what is showing in your query and what you want your query to show based on the data you provide.

 

[maytime]

Here is the SQL around the field I want to suppress duplicates:

SELECT DISTINCT [Components-Process Lines].TAG_ AS TAG_NUMBER,

Basically I am renaming "TAG_" to "TAG_NUMBER" and want to hide duplicates around just that field.

I thought I could add a WHERE statement in there to make something like this:

SELECT DISTINCT [Components-Process Lines].TAG_ WHERE (TAG_.DISTINCT = true) AS TAG_NUMBER,

But that isn't working. See the attachment for how the query is showing the records where "TAG_NUMBER" is duplicated, but the duplicates have different data in other fields.

 

[plog]

That's half the battle. Now give me what you want the output to be. Which Primary_Drawing_Number value do you want to be returned and why?

 

[maytime]

I'd be fine with the first record Access gets to being the one that shows, so the one with "..._AR" in the suffix part. That field tells us what engineering drawing that particular TAG_NUMBER is associated with, and it doesn't really matter which one of those two choices it shows.

 

[plog]

In that case I would create an aggregate query. Bring in all the fields you want to show, click the Sigma icon in the ribbon, that will cause the words 'Group By' to appear under each field you've brought in. Change everyone except the TAG_NUMBER field to 'First'. That will give you the results you want.

 

[maytime]

The aggregate method seems to conflict with some 'IIf' statements I have in the query design that I'm using to parse that TAG_NUMBER out between the hyphens.

I tried making a separate query with just "TAG_" and using the "Unique Values" set to Yes. That does filter out the duplicate TAG_ entries, but if I try to use that query as the record source in my PIPELINE_UPLOAD main query, I get a message saying:

"The specified field '[TAG_]' could refer to more than one table listed in the FROM caluse of your SQL statement"

 

[plog]

Sounds like you have [TAG_] fields in more than one data source. In your IIf statements you need to explicitly tell it what data source the [TAG_] field is in. That means change it from [TAG_] to [YourTableName].[TAG_] where YourTableName is the Table or query the [TAG_] field is in.

 

[maytime]

That cleared up that error message, thanks.

So I got it working using the 'First' method you suggested and I verified that everything is showing up. The only thing is that I had to click through a bunch of "OK"s on parameter input dialog boxes that pop up when I run the query for my query columns that are using IIf expressions to output a value. Is there a way to suppress this or is it just something I have to live with ya think?

 

[plog]

You should be able to fix them. It's either similar to the issue you had with the [TAG_] field or you are using fields that no longer exist in the query's source. I'd look through all the calculated fields and make sure that you designate a datasource for each one like you did with the [TAG_] field.

 

[maytime]

I think I just need to play with it. It worked, but then put a "First" infront of some of my fields in the query. Thanks for the help though, I think it just takes some code cleanup to get it the rest of the way.

I wish Access just had a feature to remove duplicates based on one field...seems like it would be easy for them to do.

 

[maytime]

Just an update:

I got it working the way I needed it by renaming all those columns that had "First" preceding the correct column names by using "FieldName: FieldName". For all my fields that had expressions in them, I set those to "Expression" instead of "First" in the aggregate function row. It worked just as I wanted it to after that with no error messages or parameter input prompts.

Thanks for your help, plog!