Unique records required (1 Viewer)

[Shiv]

Name trips class
a 4 1st
a 4 2nd
a 4 3rd
b 4 4th

From the above data,i want to see only those records which are unique in Name "field" . Result for the above data is :-

Name Trips Class
b 4 4th

Name "A" is more than 1 times in the data so it is not required in the result.

Access Database is attached herewith for reference please.

Thanks in advance for the support

 

[Ranman256]

make a query , set the property to UNIQUE VALUES = TRUE
only use the 2 fields
NAME, TRIPS

 

[Shiv]

thanks a lot for replying. actually it is not working and giving all the four enteries.

2. i want to make the query through SQL view.

 

[isladogs]

That's not what the OP asked for.
This works

SELECT DISTINCT Table1.[no], Table1.trips, Table1.class
FROM Table1
WHERE (((Table1.[no]) In (SELECT [no] FROM [Table1] As Tmp GROUP BY [no] HAVING Count(*)=1 )))
ORDER BY Table1.[no];

 

[Shiv]

Higher value required for each name

Name trips class Result
a 4 1st 1
a 4 2nd 2
a 4 3rd 3
b 4 4th 5

From the above data,i want to fetch the complete records for each name where he has achieved highest in "result" field. Result for the above data shall be as under:-

Name Trips Class Result
a 4 3rd 3
b 4 4th 5



Access Database is attached herewith for reference please.

Thanks in advance for the support

 

[isladogs]

Here's one method using 2 queries:

Query3:
SELECT Table1.[no], Max(Table1.weightage) AS MaxOfweightage
FROM Table1
GROUP BY Table1.[no];

then use that with Table1 in:
SELECT Table1.[no], Table1.trips, Table1.class, Table1.weightage
FROM Table1 INNER JOIN Query3 ON (Table1.weightage = Query3.MaxOfweightage) AND (Table1.[no] = Query3.[no]);

It could also be done using a subquery but i'll leave that to you

BTW: it would help if you used the same field names in your posts as in your example apps