Help with Distinct function.

[PSPZX]

I have a query that returns employee names and reservations they updated for a set date. Here's a simplified version:

Select Employee.Name, Reservation.ResID
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where Reservation.DateModified BETWEEN '200510010000' AND '200510050000'

The problem is each employee can update the same reservation several times but I only want it to display that record once. The distinct statement doesn't work because I want the EmpID to be listed more than once. Thanks.

[eliasis]

just add another column to your select statement Reservation.DateModified

[PSPZX]

If the reservation is modified twice within that timeframe, it will show up twice in the result. I need it to only show up once. Thanks.

[skhanal]

How about using DISTINCT

Select Distinct Employee.Name, Reservation.ResID
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where Reservation.DateModified BETWEEN '200510010000' AND '200510050000'

[Frank Johnson]

Questions: What do you want the outcome to be?

Do you want 1 record for an employee but you don't know if there are multiple reservations and you only want to know if he has a reservation or not between a given time frame?

Do you Want a record for ever time an employee makes a reservation change between a given time frame with the posibility of multiple reservations?

Do you want a record for each change to a reservation between a given time frame for a given employee?

Does the reservation table have multiple records for an employee?

Does the employee table have multiple records for an employee for some reason?

[PSPZX]

Questions: What do you want the outcome to be?

Do you want 1 record for an employee but you don't know if there are multiple reservations and you only want to know if he has a reservation or not between a given time frame?

Do you Want a record for ever time an employee makes a reservation change between a given time frame with the posibility of multiple reservations?

Do you want a record for each change to a reservation between a given time frame for a given employee?

Does the reservation table have multiple records for an employee?

Does the employee table have multiple records for an employee for some reason?

I want the outcome to list only the first modification of the reservation and by what employee. So the query will list the employee several times and what reservation he/she updated initially. For example, say John updated 5 reservations, two times each, for a total of 10 records. I would like the query to show 5 records of John and the initial update of each reservations.

Yes, the reservation table has an entry (by ResID) for each update. In the above case, there would be a record inserted for each time a reservation was modified.

The employee table has the employee listed only once.

[LSNicholls]

>>
The problem is each employee can update the same reservation several times but I only want it to display that record once. The distinct statement doesn't work because I want the EmpID to be listed more than once. Thanks
[..]
I want the outcome to list only the first modification of the reservation and by what employee. So the query will list the employee several times and what reservation he/she updated initially.
<<

Like other people here, I think the DISTINCT keyword *should* work because the DISTINCT would apply to the combination of employee ID + reservation ID, so you *should* get the employee appearing more than once. But it might not be optimal for making sure that only the *first* mod is shown...

... Would something like this do what you're looking for (I may be misunderstanding)?

Select Employee.Name, Reservation.ResID, 
MIN(Reservation.DateModified) AS FirstMod
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where [..]
Group By Employee.Name, Reservation.ResID

>L<