Ignore records where field is duplicated

[mvagnier]

I would like to construct a query that selects all records from a table where
the value in a specific field is unique. My preference is to leave the
original table as-is.

In hindsight, this table should have been set up to prohibit duplicate
entries in a field called "Survey Issued ID". I want to ignore any records
where the same "Survey Issued ID" appears in more than one record. (By the
way, for records where the ID is used more than once, other fields may or may
not be identical. In other words, poor keypunching quality.)

Thanks in advance for any thoughts on this!

 

[Jeff Boyce]

Are you saying that if two (or more) records share the same [Survey Issued
ID] value, you want to see NONE of them?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mvagnier]

Jeff, that is correct. Thanks in advance for taking the time to consider my
question. I am not all that swift with my depth of Access knowledge; I feel
the answer is very simple (and indeed, something I've probably also done in
the past) - it's just not one of those things that is coming to me!

Michael Vagnier

Are you saying that if two (or more) records share the same [Survey Issued
ID] value, you want to see NONE of them?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to construct a query that selects all records from a table
where
the value in a specific field is unique. My preference is to leave the
original table as-is.

In hindsight, this table should have been set up to prohibit duplicate
entries in a field called "Survey Issued ID". I want to ignore any
records
where the same "Survey Issued ID" appears in more than one record. (By
the
way, for records where the ID is used more than once, other fields may or
may
not be identical. In other words, poor keypunching quality.)

Thanks in advance for any thoughts on this!

 

[John Spencer]

SELECT *
FROM YourTable
WHERE OnlyUniqueField IN
(SELECT OnlyUniqueField
FROM YourTable
GROUP BY OnlyUniqueField
HAVING Count(OnlyUniqueField) = 1)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Jeff, that is correct. Thanks in advance for taking the time to consider my
question. I am not all that swift with my depth of Access knowledge; I feel
the answer is very simple (and indeed, something I've probably also done in
the past) - it's just not one of those things that is coming to me!

Michael Vagnier

Are you saying that if two (or more) records share the same [Survey Issued
ID] value, you want to see NONE of them?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to construct a query that selects all records from a table
where
the value in a specific field is unique. My preference is to leave the
original table as-is.

In hindsight, this table should have been set up to prohibit duplicate
entries in a field called "Survey Issued ID". I want to ignore any
records
where the same "Survey Issued ID" appears in more than one record. (By
the
way, for records where the ID is used more than once, other fields may or
may
not be identical. In other words, poor keypunching quality.)

Thanks in advance for any thoughts on this!

 

[mvagnier]

John, I have to tell you I have gotten some of my best assistance reading
your responses to the posts of others.

Although the subquery I tried to write was in the direction of what you
proposed, it just didn't cut it; your structure worked perfectly, and I thank
both you and Jeff for sharing your valuable time with the rest of us...Thanks
Again!

Michael Vagnier

SELECT *
FROM YourTable
WHERE OnlyUniqueField IN
(SELECT OnlyUniqueField
FROM YourTable
GROUP BY OnlyUniqueField
HAVING Count(OnlyUniqueField) = 1)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Jeff, that is correct. Thanks in advance for taking the time to consider my
question. I am not all that swift with my depth of Access knowledge; I feel
the answer is very simple (and indeed, something I've probably also done in
the past) - it's just not one of those things that is coming to me!

Michael Vagnier

Are you saying that if two (or more) records share the same [Survey Issued
ID] value, you want to see NONE of them?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to construct a query that selects all records from a table
where
the value in a specific field is unique. My preference is to leave the
original table as-is.

In hindsight, this table should have been set up to prohibit duplicate
entries in a field called "Survey Issued ID". I want to ignore any
records
where the same "Survey Issued ID" appears in more than one record. (By
the
way, for records where the ID is used more than once, other fields may or
may
not be identical. In other words, poor keypunching quality.)

Thanks in advance for any thoughts on this!