ACCESS FEED: RE: [MS_AccessPros] left join: how to select one record

Senin, 03 Oktober 2011

RE: [MS_AccessPros] left join: how to select one record

Michael-

strSource = "SELECT TOP 1 tblPOC.PocID, [tblPOC].[FName] & "" "" & _
[tblPOC].[LName] & "", "" & [tblPhone].[PhoneNum] AS TenInfo, " & _
"tblPocGroupLink.GroupID, tblPOC.Active" & _
" FROM (tblPOC LEFT JOIN tblPhone ON tblPOC.PocID = tblPhone.PocID) " & _
"INNER JOIN tblPocGroupLink ON tblPOC.PocID = tblPocGroupLink.PocID" & _
" WHERE (tblPocGroupLink.GroupID) = " & Me.cmboClient & _
" ORDER BY [tblPOC].[FName]"

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of michael simpson
Sent: Monday, October 03, 2011 11:03 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] left join: how to select one record

Hi there -
Working on a drop down combo box for tenants on a work order form.

At the moment I am getting multiple records when a tenant has more than one
phone number

How would I filter or change the sql to get only the first record. Thanks
Michael

strSource = " SELECT tblPOC.PocID, [tblPOC].[FName] & "" "" & [tblPOC].[LName] &
"", "" & [tblPhone].[PhoneNum] AS TenInfo, tblPocGroupLink.GroupID,
tblPOC.Active" & _
            " FROM (tblPOC LEFT JOIN tblPhone ON tblPOC.PocID = tblPhone.PocID)
INNER JOIN tblPocGroupLink ON tblPOC.PocID = tblPocGroupLink.PocID" & _
            " WHERE (tblPocGroupLink.GroupID) = " & Me.cmboClient & _
            " ORDER BY [tblPOC].[FName]"
Me.cmboTenantID.RowSource = strSource
Me.cmboTenantID = Null

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links