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!

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)




"SD" wrote:

> 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!

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

> > TicketNo Time Colour
> > 2 8:00am Red
> > 2 8:30am Green
> > 2 4:00pm Yellow
> > 3 11:00am Yellow
> > 4 4:30pm Blue
> > 4 5:00pm Red
> > 5 10:30am Green
> >

How would you get it to return the colour that tallies with the earliest time?
> >
> > 2 8:00am Red
> > 3 11:00am Yellow
> > 4 4:30pm Blue
> > 5 10:30am Green
> >

Hope someone can help.

Cheers

Gordon

See The Access Web article:

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

--
HTH
Van T. Dinh
MVP (Access)



"Gordon Comstock" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> > > TicketNo Time Colour
> > > 2 8:00am Red
> > > 2 8:30am Green
> > > 2 4:00pm Yellow
> > > 3 11:00am Yellow
> > > 4 4:30pm Blue
> > > 4 5:00pm Red
> > > 5 10:30am Green
> > >
>
> How would you get it to return the colour that tallies with the earliest
time?
> > >
> > > 2 8:00am Red
> > > 3 11:00am Yellow
> > > 4 4:30pm Blue
> > > 5 10:30am Green
> > >
>
> Hope someone can help.
>
> Cheers
>
> Gordon

Exactly what I needed. Thank you very much.

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

Cheers

Gordon