From: Bob Vance on

I am in need of an expression for my query, HorseID can have multiple
OwnerID as there could be 6 shares in a Horse, What I need is a query that
will list all OwnerName in one Column, qryOwnerName.HorseName is from
another Query
TblHorseInfo
HorseID / Number
OwnerID / Number

QryOwnerName
HorseID, HorseName ,OwnerID, OwnerName
--
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3


From: Steve on
Bob,

Usually you would select the horse in a form. Say the form's name is
PFrmSelectHorse and you select the horse via a combobox that returns HorseID
for the selected horse. Say the name of the combobox os SelectHorse. Put
this expression in the criteria of HorseID in your query:
Forms!PFrmSelectHorse!SelectHorse

Steve
santus(a)penn.com



"Bob Vance" <rjvance(a)ihug.co.nz> wrote in message
news:uHsd1vY6KHA.3656(a)TK2MSFTNGP06.phx.gbl...
>
> I am in need of an expression for my query, HorseID can have multiple
> OwnerID as there could be 6 shares in a Horse, What I need is a query that
> will list all OwnerName in one Column, qryOwnerName.HorseName is from
> another Query
> TblHorseInfo
> HorseID / Number
> OwnerID / Number
>
> QryOwnerName
> HorseID, HorseName ,OwnerID, OwnerName
> --
> Thanks in advance for any help with this......Bob
> MS Access 2007 accdb
> Windows XP Home Edition Ver 5.1 Service Pack 3
>
>


From: John W. Vinson on
On Sun, 2 May 2010 12:11:37 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote:

>
>I am in need of an expression for my query, HorseID can have multiple
>OwnerID as there could be 6 shares in a Horse, What I need is a query that
>will list all OwnerName in one Column, qryOwnerName.HorseName is from
>another Query
>TblHorseInfo
>HorseID / Number
>OwnerID / Number
>
>QryOwnerName
>HorseID, HorseName ,OwnerID, OwnerName

If there are multiple OwnerIDs you wouldn't want them in the query, would you?
I'm guessing you'ld like to see

3213, "Alpo", "Mo Howard, Larry Fine, Curly Howard"

perhaps?

If so, you'll need some VBA code. This is quite difficult with a simple query,
but you can get the fConcatChild VBA function from

http://www.mvps.org/access/modules/mdl0004.htm

Copy the code into a new Module; save it as basStrings. Follow the
instructions in the website and you should be able to get this result.
--

John W. Vinson [MVP]
From: Bob Vance on

"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:anhpt5pb7cg7tve5qtm7rdbuqg89sil0c5(a)4ax.com...
> On Sun, 2 May 2010 12:11:37 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote:
>
>>
>>I am in need of an expression for my query, HorseID can have multiple
>>OwnerID as there could be 6 shares in a Horse, What I need is a query that
>>will list all OwnerName in one Column, qryOwnerName.HorseName is from
>>another Query
>>TblHorseInfo
>>HorseID / Number
>>OwnerID / Number
>>
>>QryOwnerName
>>HorseID, HorseName ,OwnerID, OwnerName
>
> If there are multiple OwnerIDs you wouldn't want them in the query, would
> you?
> I'm guessing you'ld like to see
>
> 3213, "Alpo", "Mo Howard, Larry Fine, Curly Howard"
>
> perhaps?
>
> If so, you'll need some VBA code. This is quite difficult with a simple
> query,
> but you can get the fConcatChild VBA function from
>
> http://www.mvps.org/access/modules/mdl0004.htm
>
> Copy the code into a new Module; save it as basStrings. Follow the
> instructions in the website and you should be able to get this result.
> --
>
> John W. Vinson [MVP]
Thanks John,
Thats excatly what i would like to see maybe like this
"Alpo" HorseName "Mo,Fine,Howard" OwnerLastName
I have a query that may be tweaked below

SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
tblOwnerInfo.OwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfo.OwnerID;


From: Bob Vance on

> Thanks John,
> Thats excatly what i would like to see maybe like this
> "Alpo" HorseName "Mo,Fine,Howard" OwnerLastName
> I have a query that may be tweaked below
>
> SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID,
> funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
> tblOwnerInfo.OwnerLastName
> FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
> tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
> tblOwnerInfo.OwnerID;
>
>
Thanks John ,Actually this query might help.........Regards Bob

SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID,
funGetHorse(0,[tblHorseInfo].[HorseID],False) AS HorseName,
tblOwnerInfo.OwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfo.OwnerID;