Comparing values in same field to return only first unique values

[Guest]

Not sure how I would need to create this and haven't come across the answer
in my searches or help in access. I would like to thank anyone who can
assist me on this one, my brain has just about given up on this!

What I need to do is take the first value found in the field (say ticket
number) and compare the next value to make sure that it is a different
number. If it is the same number I want to make it not display any of the
next values until it finds something different.


Example:

TicketNo Time
2 8:00am
2 8:30am
2 4:00pm
3 11:00am
4 4:30pm
4 5:00pm
5 10:30am

and I need the query to return

2 8:00am
3 11:00am
4 4:30pm
5 10:30am

Hope you guys can help me out on this one!

 

[Guest]

Try the (Query with) the SQL String like:

SELECT [TicketNo], Min([Time]) As EarliestTimeForTicket
FROM [YourTable]
GROUP BY [TicketNo]

HTH
Van T. Dinh
MVP (Access)

 

[Gordon Comstock]

I'd like to ask a supplementary question to this thread.

What if SD's table had an extra field called colour and the data looked like this

How would you get it to return the colour that tallies with the earliest time?
Hope someone can help.

Cheers

Gordon

 

[Van T. Dinh]

See The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

 

[Gordon Comstock]

Exactly what I needed. Thank you very much.

I usually look there first but wasn't quite sure what to search for.

Cheers

Gordon