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

Hi Michael

Do you mean you want to show each name only once, with any one of the
related phone numbers (if there are any)?

Try using a subquery to get the phone number:

strSource = "SELECT tblPOC.PocID, [tblPOC].[FName] & ' ' & [tblPOC].[LName]
& " & _
"(', ' + (Select First([PhoneNum]) from tblPhone where
tblPhone.PocID=tblPOC.PocID)) AS TenInfo, " & _
"tblPocGroupLink.GroupID, tblPOC.Active " & _
" FROM tblPOC INNER JOIN tblPocGroupLink ON tblPOC.PocID =
tblPocGroupLink.PocID" & _
" WHERE (tblPocGroupLink.GroupID) = " & Me.cmboClient & _
" ORDER BY [tblPOC].[FName]"

Note that I have used single quotes in the query because SQL is fine with
them and it makes the VBA a little easier to read.

Best wishes,
Graham

> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of michael
simpson
> Sent: Tuesday, 4 October 2011 10:03
> 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