Query Staff Events, but only show the staff member once in the list

[kengooch]

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]

Remove the event from the selected columns and select Unique Value = Yes or use DISTINCT in the SQL statement.

 

[plog]

Use a Totals query, make GROUP BY appear under every field in your query except tstffEvent which should be WHERE.

 

[arnelgp]

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]

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]

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.