Finding Unique Data

[Rumy]

I have 3 coumns in a table. all 3 columns have duplicate data. But I want to
find unique data in column A only. How do i do this in ACESS 2007 ? Please
help.

 

[Stefan Hoffmann]

hi Rumy,

I have 3 coumns in a table. all 3 columns have duplicate data. But I want to
find unique data in column A only. How do i do this in ACESS 2007 ? Please
help.

Maybe so:

SELECT *
FROM yourTable
WHERE (A <> B) AND (A <> C)


mfG
--> stefan <--

 

[golfinray]

Under query types, you have one for Find Umatched Data. That will tell you
which data is unique.

 

[Rumy]

No That does not help. Find Unmatched data is to find unmatched data in 2
different tables or queries. Thanks.

 

[Rumy]

Could you be more specific please. Are you asking me to create a query ?
Wherre shoudl I place that statement ? Thanks

 

[Stefan Hoffmann]

Could you be more specific please. Are you asking me to create a query ?
Wherre shoudl I place that statement ? Thanks Sure,

Create query, add your table, switch to SQL view and append the WHERE
clause at the end after the FROM yourTableName. Replace A, B and C with
your concrete field names.

mfG
--> stefan <--

 

[Douglas J. Steele]

What's your definition of "unique data in column A only"?

Do you mean you want to know the various values that only occur once in
column A?

The easiest way would be to go to create a query but not select any tables
and switch to SQL view. Once in SQL view, type something like:

SELECT [ColumnA]
FROM [MyTable]
GROUP BY [ColumnA]
HAVING Count(*) = 1

(replace ColumnA and MyTable with the actual names)

 

[KARL DEWEY]

In design view do this --
FIELD ColumnA ColumnA
Click on icon that looks like an 'M' on its side - ∑
The third row of grid will read TOTAL:
Change the GROUP BY under the second ColumnA to COUNT.
Under the second ColumnA in the CRITERIA row put the number 1.

 

[Rumy]

Thanks KARL that is take me some what there. The problem is when i do that
its giving me unique data including the other 2 colums which also has
duplicates. but if i remove the other two columns and follow ur steps its
giving the correct unique data. then how to i get the other 2 columns into
this query ??

 

[Rumy]

Okay may be i gace the wrong specs.

Okay let me be more specific. i have 3 columns.
Claim No, Batch Cat and Suspension. I have 45000 lines.
example :- I see 100 lines of the same claim no with the same batch cat and
suspension.
But I need only one line.of this.

What's your definition of "unique data in column A only"?

Do you mean you want to know the various values that only occur once in
column A?

The easiest way would be to go to create a query but not select any tables
and switch to SQL view. Once in SQL view, type something like:

SELECT [ColumnA]
FROM [MyTable]
GROUP BY [ColumnA]
HAVING Count(*) = 1

(replace ColumnA and MyTable with the actual names)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I have 3 coumns in a table. all 3 columns have duplicate data. But I want
to
find unique data in column A only. How do i do this in ACESS 2007 ? Please
help.

.

 

[Douglas J. Steele]

So you're saying that all three fields are duplicated?

Change your Select statement from SELECT Field1, Field2, Field3 to SELECT
DISTINCT Field1, Field2, Field3

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Okay may be i gace the wrong specs.

Okay let me be more specific. i have 3 columns.
Claim No, Batch Cat and Suspension. I have 45000 lines.
example :- I see 100 lines of the same claim no with the same batch cat
and
suspension.
But I need only one line.of this.

What's your definition of "unique data in column A only"?

Do you mean you want to know the various values that only occur once in
column A?

The easiest way would be to go to create a query but not select any
tables
and switch to SQL view. Once in SQL view, type something like:

SELECT [ColumnA]
FROM [MyTable]
GROUP BY [ColumnA]
HAVING Count(*) = 1

(replace ColumnA and MyTable with the actual names)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I have 3 coumns in a table. all 3 columns have duplicate data. But I
want
to
find unique data in column A only. How do i do this in ACESS 2007 ?
Please
help.

.

 

[KARL DEWEY]

Post sample data and example of what you want for query results.

 

[Rumy]

Good Morning Doug, i appreciate your patience while trying to solve this
puzzle for me. I am still having trouble getting the data i need. This is
what i have in my query, SQL statement.

SELECT DISTINCT [claim no],[batch cat],[suspension]
FROM [Suspension D5231]
GROUP BY [claim no]
HAVING Count(*) = 1

Now im getting this error msg.

" you tried to execute a query that does not include the specific expression
' batch cat' as part of an aggregate function.

thanks.

So you're saying that all three fields are duplicated?

Change your Select statement from SELECT Field1, Field2, Field3 to SELECT
DISTINCT Field1, Field2, Field3

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Okay may be i gace the wrong specs.

Okay let me be more specific. i have 3 columns.
Claim No, Batch Cat and Suspension. I have 45000 lines.
example :- I see 100 lines of the same claim no with the same batch cat
and
suspension.
But I need only one line.of this.

What's your definition of "unique data in column A only"?

Do you mean you want to know the various values that only occur once in
column A?

The easiest way would be to go to create a query but not select any
tables
and switch to SQL view. Once in SQL view, type something like:

SELECT [ColumnA]
FROM [MyTable]
GROUP BY [ColumnA]
HAVING Count(*) = 1

(replace ColumnA and MyTable with the actual names)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I have 3 coumns in a table. all 3 columns have duplicate data. But I
want
to
find unique data in column A only. How do i do this in ACESS 2007 ?
Please
help.


.

.

 

[Rumy]

Okay here you go KARL.i need only one line out of the 1st three. plus lines 4
and 5.Thanks.Hope you can figure out a way out now.

Claim # batch cat suspension
123456789 b abc
123456789 b abc
123456789 b abc
123456789 k efg
123456789 l efg

 

[Douglas J. Steele]

My previous suggestion was that all you needed was

SELECT DISTINCT [claim no],[batch cat],[suspension]
FROM [Suspension D5231]

Since DISTINCT ensures that there's only going to be one instance for each
combination, there's no need for the GROUP BY and HAVING clauses.


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Good Morning Doug, i appreciate your patience while trying to solve this
puzzle for me. I am still having trouble getting the data i need. This is
what i have in my query, SQL statement.

SELECT DISTINCT [claim no],[batch cat],[suspension]
FROM [Suspension D5231]
GROUP BY [claim no]
HAVING Count(*) = 1

Now im getting this error msg.

" you tried to execute a query that does not include the specific
expression
' batch cat' as part of an aggregate function.

thanks.

So you're saying that all three fields are duplicated?

Change your Select statement from SELECT Field1, Field2, Field3 to SELECT
DISTINCT Field1, Field2, Field3

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Okay may be i gace the wrong specs.

Okay let me be more specific. i have 3 columns.
Claim No, Batch Cat and Suspension. I have 45000 lines.
example :- I see 100 lines of the same claim no with the same batch
cat
and
suspension.
But I need only one line.of this.

:

What's your definition of "unique data in column A only"?

Do you mean you want to know the various values that only occur once
in
column A?

The easiest way would be to go to create a query but not select any
tables
and switch to SQL view. Once in SQL view, type something like:

SELECT [ColumnA]
FROM [MyTable]
GROUP BY [ColumnA]
HAVING Count(*) = 1

(replace ColumnA and MyTable with the actual names)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I have 3 coumns in a table. all 3 columns have duplicate data. But I
want
to
find unique data in column A only. How do i do this in ACESS 2007 ?
Please
help.


.

.

 

[Rumy]

Hey Doug, Finally my mystery is solved. Thank you so very much !!!

My previous suggestion was that all you needed was

SELECT DISTINCT [claim no],[batch cat],[suspension]
FROM [Suspension D5231]

Since DISTINCT ensures that there's only going to be one instance for each
combination, there's no need for the GROUP BY and HAVING clauses.


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Good Morning Doug, i appreciate your patience while trying to solve this
puzzle for me. I am still having trouble getting the data i need. This is
what i have in my query, SQL statement.

SELECT DISTINCT [claim no],[batch cat],[suspension]
FROM [Suspension D5231]
GROUP BY [claim no]
HAVING Count(*) = 1

Now im getting this error msg.

" you tried to execute a query that does not include the specific
expression
' batch cat' as part of an aggregate function.

thanks.

So you're saying that all three fields are duplicated?

Change your Select statement from SELECT Field1, Field2, Field3 to SELECT
DISTINCT Field1, Field2, Field3

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Okay may be i gace the wrong specs.

Okay let me be more specific. i have 3 columns.
Claim No, Batch Cat and Suspension. I have 45000 lines.
example :- I see 100 lines of the same claim no with the same batch
cat
and
suspension.
But I need only one line.of this.

:

What's your definition of "unique data in column A only"?

Do you mean you want to know the various values that only occur once
in
column A?

The easiest way would be to go to create a query but not select any
tables
and switch to SQL view. Once in SQL view, type something like:

SELECT [ColumnA]
FROM [MyTable]
GROUP BY [ColumnA]
HAVING Count(*) = 1

(replace ColumnA and MyTable with the actual names)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I have 3 coumns in a table. all 3 columns have duplicate data. But I
want
to
find unique data in column A only. How do i do this in ACESS 2007 ?
Please
help.


.



.

.

 

[erdalllamine]

Good Morning Doug, i appreciate your patience while trying to solve this
puzzle for me. I am still having trouble getting the data i need. This is
what i have in my query, SQL statement.

SELECT DISTINCT [claim no],[batch cat],[suspension]
FROM [Suspension D5231]
GROUP BY [claim no]
HAVING Count(*) = 1

Now im getting this error msg.

" you tried to execute a query that does not include the specific
expression
' batch cat' as part of an aggregate function.

thanks.

So you're saying that all three fields are duplicated?

Change your Select statement from SELECT Field1, Field2, Field3 to SELECT
DISTINCT Field1, Field2, Field3

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Okay may be i gace the wrong specs.

Okay let me be more specific. i have 3 columns.
Claim No, Batch Cat and Suspension. I have 45000 lines.
example :- I see 100 lines of the same claim no with the same batch
cat
and
suspension.
But I need only one line.of this.

:

What's your definition of "unique data in column A only"?

Do you mean you want to know the various values that only occur once
in
column A?

The easiest way would be to go to create a query but not select any
tables
and switch to SQL view. Once in SQL view, type something like:

SELECT [ColumnA]
FROM [MyTable]
GROUP BY [ColumnA]
HAVING Count(*) = 1

(replace ColumnA and MyTable with the actual names)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I have 3 coumns in a table. all 3 columns have duplicate data. But I
want
to
find unique data in column A only. How do i do this in ACESS 2007 ?
Please
help.


.

.