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

 

[MGFoster]

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??

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use GROUP BY or DISTINCTROW:

SELECT G.ID, G.FirstName, G.LastName
FROM tblGuest As G INNER JOIN tblServices As S ON G.ID=S.ID
WHERE S.ServicesDate = Date()-1
GROUP BY G.ID, G.FirstName, G.LastName

Or

SELECT DISTINCTROW G.ID, G.FirstName, G.LastName
FROM tblGuest As G INNER JOIN tblServices As S ON G.ID=S.ID
WHERE S.ServicesDate = Date()-1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSa8XR4echKqOuFEgEQJJJQCfX213yR4/2un1xSHz66zvfb2qjUwAmwW+
ip+SZHSjUSReMU8ypZtBgF7q
=nD4N
-----END PGP SIGNATURE-----