Get names from previous date


R

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

Advertisements

J

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
'====================================================
 
Ad

Advertisements

R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top