select distinct

  • Thread starter Thread starter Seriola Dumerili
  • Start date Start date
S

Seriola Dumerili

Hello everyone!
I am new in Access database and I have a problem with simple query. In one
table with duplicate key (id_guest) I am trying to select one record with
same value in id_guest field.

SELECT DISTINCT Guest.id_guest, Guest.date_arrival
FROM Guest
WHERE (((Guest.date_arrival)>#12/31/2002# And
(Guest.date_arrival)<#11/7/2003#));

The problem is that I am receiving more than one record per key in the
query.
Any help?
 
The DISTINCT applies across the combination of all columns
returned hence the reason why you are receiving multiple
rows for the same guest. If you want the list of guests
without dates then
SELECT DISTINCT Guest.id_guest
FROM Guest
WHERE (((Guest.date_arrival)>#12/31/2002# And
(Guest.date_arrival)<#11/7/2003#));
would do the trick.

If you wanted the last arrival date, then
SELECT Guest.id_guest, Max(Guest.date_arrival)
FROM Guest T1
GROUP BY Guest.id_guest
WHERE (((Guest.date_arrival)>#12/31/2002# And
(Guest.date_arrival)<#11/7/2003#));
should be ok.

Hope This Helps
Gerald Stanley MCSD
 
If the guest has been there more than once in your time period, you're going
to get one record for each visit. That's because the DISTINCT clause will
return one row for each unique combination of id_guest and date_arrival.

To get a list of distrinct guests, you'll need to remove date_arrival from
that query.

You can always use that as a subquery to subsequently pull additional
information about your guests.
 
Back
Top