Filtering for Unique Fields in a Table

[shilpashetty27]

Hi,

I've been trying to accomplish the following in MS Access:

From the following table, I'm trying to filter out any duplicate 'Column B' values. For the unique 'Column B' values, I want my query to return the corresponding 'Column C' values.



Column A Column B Column C
1 abcd stuv
2 efgh xyzw
3 efgh qrst
4 ijkl mnop





The results of my query should look like this:

Column A Column B Column C
1 abcd stuv
2 efgh xyzw
3 ijkl mnop




Any help on how I can go about doing this is appreciated.
Thanks in advance!
Cheers,
Shilpa

 

[Kiwiman]

You can add totals to your query, grouping by Column B and First on Column C

Take it Easy
Man with one chopstick go hungry

 

[shilpashetty27]

Thanks for your response, but an example would clarify things better. I'm not sure I followed your suggestion. Please specify the code if you can.

cheers!

 

[Kiwiman]

Howzit

In the design view of your query, click on the totals button on your Query Design tool bar (the sigma button).

The default totals option is Group. Leave this option for Col B, but select "First" for column C. Alternativly, paste the below into the sql view of your query
(you will need to adapt for your environment)

SELECT YourTable.ColumnB, First(YourTable.ColumnC) AS COlC
FROM YourTable
GROUP BY YourTable.ColumnB

Take it Easy
Man with one chopstick go hungry