Query Expression Help!

[Bob Vance]

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

 

[Steve]

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
(e-mail address removed)

 

[John W. Vinson]

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.

 

[Bob Vance]

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.

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,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

 

[Bob Vance]

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,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

Thanks John ,Actually this query might help.........Regards Bob

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

 

[John W. Vinson]

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,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

If you download fConcatChild from the website, put it into a new module,
compile it and save it, you should be able to use

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

Untested air code of course!

 

[Bob Vance]

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,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

John I have created this and it works but i get
Error 3075 Syntax Error (Missing Operator) In query Expression HorseID=
but after oking it 7 times the query is correct?
Regards Bob

SELECT DISTINCT tblHorseDetails.HorseID,
ConcatRelated("OwnerID","tblHorseDetails","HorseID = " & [HorseID]) AS
MyClientList
FROM tblHorseDetails;

 

[John W. Vinson]

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,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

John I have created this and it works but i get
Error 3075 Syntax Error (Missing Operator) In query Expression HorseID=
but after oking it 7 times the query is correct?
Regards Bob

SELECT DISTINCT tblHorseDetails.HorseID,
ConcatRelated("OwnerID","tblHorseDetails","HorseID = " & [HorseID]) AS
MyClientList
FROM tblHorseDetails;

Since you're using a different function and a different query from what I
suggested I really cannot comment. Do you have a function named ConcatRelated
in a Module? If you type Ctrl-G and type

?ConcatRelated("OwnerID","tblHorseDetails","HorseID = 333")

using a valid HorseID in the Immediate window, do you get the names you
expect?

 

[Bob Vance]

If you download fConcatChild from the website, put it into a new module,
compile it and save it, you should be able to use

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

Untested air code of course!

Thanks John It is showing My Horses and Owners but it is not putting my
owners on the same line , I am getting multiple records for my same Horse
with different Owners.........Regards Bob I have fConcatChild in a Module
[basConatChild]

 

[Bob Vance]

Since you're using a different function and a different query from what I
suggested I really cannot comment. Do you have a function named
ConcatRelated
in a Module? If you type Ctrl-G and type

?ConcatRelated("OwnerID","tblHorseDetails","HorseID = 333")

using a valid HorseID in the Immediate window, do you get the names you
expect?

Thanks John that was an earlier attempt I am using your code and function in
a module
I did that test and got a
Compile Error
Expected:=

Thanks Bob

 

[Bob Vance]

Since you're using a different function and a different query from what I
suggested I really cannot comment. Do you have a function named
ConcatRelated
in a Module? If you type Ctrl-G and type

?ConcatRelated("OwnerID","tblHorseDetails","HorseID = 333")

using a valid HorseID in the Immediate window, do you get the names you
expect?

John Now Im getting
Compile Error
Sub or Function not defined
Regards Bob

 

[Bob Vance]

Since you're using a different function and a different query from what I
suggested I really cannot comment. Do you have a function named
ConcatRelated
in a Module? If you type Ctrl-G and type

?ConcatRelated("OwnerID","tblHorseDetails","HorseID = 333")

using a valid HorseID in the Immediate window, do you get the names you
expect?

Yes John ConcatRelated is giving me the correct OwnerID Number
?ConcatRelated("OwnerID","tblHorseDetails","HorseID = 75")
36, 27
So what i need is a way off showing the Owners (OwnerLastName) with that ID
number in a query!
Regards Bob

 

[John W. Vinson]

Thanks John It is showing My Horses and Owners but it is not putting my
owners on the same line , I am getting multiple records for my same Horse
with different Owners.........Regards Bob I have fConcatChild in a Module
[basConatChild]

Please post the query SQL.

 

[John W. Vinson]

John Now Im getting
Compile Error
Sub or Function not defined
Regards Bob

It would help quite a lot if a) you would actually read and apply my
suggestions and b) post a readable description of what you have done.

I never suggested using ConcatRelated.

Good luck, but you're making it very difficult to be helpful.

 

[Bob Vance]

Thanks John It is showing My Horses and Owners but it is not putting my
owners on the same line , I am getting multiple records for my same Horse
with different Owners.........Regards Bob I have fConcatChild in a Module
[basConatChild]

Please post the query SQL.

Thanks John I am using fConcatChild, and I am getting 3 records for one
HorseID if he has 3 OwnerID's
Regards Bob
My Query
SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

 

[John W. Vinson]

Thanks John It is showing My Horses and Owners but it is not putting my
owners on the same line , I am getting multiple records for my same Horse
with different Owners.........Regards Bob I have fConcatChild in a Module
[basConatChild]

Please post the query SQL.

Thanks John I am using fConcatChild, and I am getting 3 records for one
HorseID if he has 3 OwnerID's
Regards Bob
My Query
SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

That's because a) you're not using fConcatChild in your query and b) you're
including tblOwnerInfo in the query.

Try using the query I suggested:

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

Note that this does NOT join the OwnerInfo table (which you don't want,
because you don't want to see every owner record) and it DOES actually *call*
the fConcatChild function (which does you no good if you have it but don't use
it).

I'm not sure I understand the tblHorseDetails.OwnerID though. I may have to
google back through this thread (or these threads) and figure out your table
relationships, and don't have time to do so today or tomorrow.

 

[Bob Vance]

Thanks John, Used your code and same result record for each OwnerID in A
HorseID

tblHorseDetails 3 Fields
HorseID - Horses ID
OwnerID - Owners ID
Percenatage - % amount owmned in Horse

tblHorseDetails Many Fields (No Owner/s ID contected with the horse here)
HorseName
Sire
Dam
DateOf Birth
Colour
Brand
.....And So On!

Thanks John It is showing My Horses and Owners but it is not putting my
owners on the same line , I am getting multiple records for my same
Horse
with different Owners.........Regards Bob I have fConcatChild in a
Module
[basConatChild]

Please post the query SQL.

Thanks John I am using fConcatChild, and I am getting 3 records for one
HorseID if he has 3 OwnerID's
Regards Bob
My Query
SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
tblOwnerInfwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfwnerID;

That's because a) you're not using fConcatChild in your query and b)
you're
including tblOwnerInfo in the query.

Try using the query I suggested:

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

Note that this does NOT join the OwnerInfo table (which you don't want,
because you don't want to see every owner record) and it DOES actually
*call*
the fConcatChild function (which does you no good if you have it but don't
use
it).

I'm not sure I understand the tblHorseDetails.OwnerID though. I may have
to
google back through this thread (or these threads) and figure out your
table
relationships, and don't have time to do so today or tomorrow.

 

[Bob Vance]

Thanks John, Used your code and same result record for each OwnerID in A
HorseID

tblHorseDetails 3 Fields
HorseID - Horses ID
OwnerID - Owners ID
Percenatage - % amount owmned in Horse

tblHorseDetails Many Fields (No Owner/s ID contected with the horse here)
HorseName
Sire
Dam
DateOf Birth
Colour
Brand
....And So On!

TblHorseDetails
HorseID OwnerID Percentage
1 6 50%
1 8 50%
2 3 33.3%
2 7 33.3%
2 8 33.3%

Regards Bob

 

[Bob Vance]

Thanks John, Used your code and same result record for each OwnerID in A
HorseID

tblHorseDetails 3 Fields
HorseID - Horses ID
OwnerID - Owners ID
Percenatage - % amount owmned in Horse

tblHorseDetails Many Fields (No Owner/s ID contected with the horse here)
HorseName
Sire
Dam
DateOf Birth
Colour
Brand
....And So On!

***OOPS bottom table should read tblHorseInfo not tblHorseDetails****

 

[John W. Vinson]

Thanks John, Used your code and same result record for each OwnerID in A
HorseID

tblHorseDetails 3 Fields
HorseID - Horses ID
OwnerID - Owners ID
Percenatage - % amount owmned in Horse

tblHorseDetails Many Fields (No Owner/s ID contected with the horse here)
HorseName
Sire
Dam
DateOf Birth
Colour
Brand

ok... you'll need to create a query qryOwnerName joining HorseDetails to
OwnerData, including the HorseID, OwnerID and the OwnerName that you want to
concatenate. I haven't tested it but try

SELECT tblHorseInfo.HorseID,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
fConcatChild("qryOwnerName", "HorseID", "OwnerLastName", "Long",
tblHorseInfo.HorseID)
FROM tblHorseInfo;