Get names from previous date

[RussCRM]

I have two tables, "tblServices" and "tblGuest"

I want to get a list of all the names that had an entry (service) for
the previous date ( Date()-1 ). I'm using ID, FirstName, LastName
from tblGuest and ServicesDate = Date()-1 from tblServices for my
query. However, guests have multiple services each day and when I do
this query I get each name multiple times.

My goal is a list of each person who had a service yesterday but I
want their name to be on the list only once, regardless of how many
services they received. Is there a way to do this??

Thanks!

 

[John Spencer]

One way is to use SELECT DISTINCT to return the unique records

SELECT DISTINCT TblGuest.*
FROM tblGuest INNER JOIN tblServices
ON tblGuest.ID = tblServices.GuestID
WHERE TblServices.ServiceDate = Date()-1

Another is to use a query like

SELECT tblGuest.*
FROM tblGuest
WHERE tblGuest.Id in
(SELECT GuestID
FROM tblServices
WHERE ServicesDates = Date()-1)

Note that in both cases, you cannot return any fields from tblServices


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Rob Parker]

Add the DISTINCT keyword to your query. In SQL view, it will be something
like:
SELECT DISTINCT tblGuest.ID, tblGuest.FirstName, ...
FROM ...
...

In the query design grid, you do this by setting Unique Values to Yes in the
query's property box (right-click any blank space in the top section of the
query design grid to get to the Property dialog).

HTH,

Rob