Prev: hidding group commands in ribbon and setting password with vb
Next: Running A2003 and A2007 on same machine
From: Bob Vance on 1 May 2010 20:11

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 1 May 2010 20:27
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 1 May 2010 20:33
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 1 May 2010 21:50

"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 1 May 2010 22:06

> 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;


 |  Next  |  Last
Pages: 1 2 3 4 5 6
Prev: hidding group commands in ribbon and setting password with vb
Next: Running A2003 and A2007 on same machine