Query Staff Events, but only show the staff member once in the list (1 Viewer)

kengooch

Member
Local time
Today, 02:33
Joined
Feb 29, 2012
Messages
137
I have a query that looks at a list of tEvents it is a child of tStaff so every staff person might have 3 or 5 or more events associated with their name. So I have two tables and the tStaff table is joined to the tEvents table by a linked field tStfLnk.

So... I need to see a list of staff that has no events assigned and also those who have Training 1 or Conference 1 or Seminar 3 or Null
When I create the query to look at a specific subset of events it shows me all of the events, but I get a line for every event.

tStfName tStfeMail tStfPhone tStfEvent
Bunny, E e.bunny@gmail.com ###-###-####
Jones, R r.jones@gmail.com ###-###-#### Training 1
Jones, R r.jones@gmail.com ###-###-#### Conference 1
Jones, R r.jones@gmail.com ###-###-#### Seminar 3
Santa, C c.santa@gmail.com ###-###-####
Smith, J j.smith@gmail.com ###-###-#### Training 1
Smith, J j.smith@gmail.com ###-###-#### Conference 1
Smith, J j.smith@gmail.com ###-###-#### Seminar 7

I only need to see one line, I don't care which event shows, I just need the name and phone number to call and leave a reminder or email a reminder. I tried using the Totals option but couldn't figure out which choice to make

What I want to see would be like this
tStfName tStfeMail tStfPhone tStfEvent
Bunny, E e.bunny@gmail.com ###-###-####
Jones, R r.jones@gmail.com ###-###-#### Training 1
Santa, C c.santa@gmail.com ###-###-####
Smith, J j.smith@gmail.com ###-###-#### Seminar 7

How can I get the query to only show one line?

Thanks
Ken

Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:33
Joined
Oct 29, 2018
Messages
21,357
Remove the event from the selected columns and select Unique Value = Yes or use DISTINCT in the SQL statement.
 

plog

Banishment Pending
Local time
Today, 04:33
Joined
May 11, 2011
Messages
11,611
Use a Totals query, make GROUP BY appear under every field in your query except tstffEvent which should be WHERE.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:33
Joined
May 7, 2009
Messages
19,169
SELECT tStfName, First(tStfeMail) As EMail, First(tStfPhone) As Phone, First(tStfEvent) As FirstOfEvent
From tStaff Left Join tEvent
On tStaff.tStfLnk = tEvent.tStfLnk
Group by tStfName;
 

oleronesoftwares

Passionate Learner
Local time
Today, 02:33
Joined
Sep 22, 2014
Messages
1,159
Select distinctrow tStfName, First(tStfeMail) As EMail, First(tStfPhone) As Phone, First(tStfEvent) As FirstOfEvent
From tStaff Left Join tEvent
On tStaff.tStfLnk = tEvent.tStfLnk
Group by tStfName;
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:33
Joined
Feb 19, 2002
Messages
42,970
If you want to be able to distinguish between staff that have events and those that don't, use a count() function or use arnel's solution to show the first event.

Distinctrow is not necessary. Grouping is sufficient.
 

Users who are viewing this thread

Top Bottom