Preventing Repeat Values in only one Field

[IainMc2013]

I have a query which generates a full list of times and appointments for a given day from two separate dates using a right join and enters a "-" in the field for unallocated time slots using an ISNULL function.

I want to refine the query further so that if a single task/appointment is block-booked in multiple adjacent time slots, I only want to return the start time of the first slot, the end time of the last slot and display the task name once.

The intention is that this will be used to create a daily timetable list in an Access (2000) report with the date being specified in a Combo box in a form in which it will be embedded - though I'm not concerned about the form/report design at the moment, only how to do the query.

I've tried several variations of SELECT DISTINCT and using nested queries to no avail.

 

[CJ_London]

Hi Iain,

Recommend you do look at your report design because it provides this functionality in the grouping/sorting options.

It will be a lot easier than trying to get a query to do this.

If you do want it in your query - why not use the start/end times in your appointment record?