Comparing one value in a field to the next to eliminate repeat dat

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

 

[Nikos Yannacopoulos]

Start making a new query in design view; add the table and drag the two
fields down to the grid. This done, go to menu item View > Totals and notice
the new line headed Totals that appears in the grid, with total function
Group By defaulted in for all fields. Now change that to First under field
Time, leaving the default Group By under TicketNo, go to datasheet view and
notice what happens. You could also use Min, Max ot Last instead of First,
getting different results. Experiment with it and you'll get the idea.

Note: if your field name is indeed Time, you will run into problems sooner
or later, because Time is a reserved word in Access (a function that returns
the current system time). Change the name of the field in the table and
everywhere else it appears to something else, before you proceed with your
design. This kind of mistake is most commonly committed with fields named
Date.

HTH,
Nikos