Query Expression Help!

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

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
 
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)
 
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
Click to expand...

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 said:
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.
Click to expand...
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,
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;
Click to expand...
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;
 
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;
Click to expand...

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,
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;
Click to expand...
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;
 
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;
Click to expand...
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;
Click to expand...

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?
 
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!
Click to expand...

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]
 
John W. Vinson said:
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?
Click to expand...

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
 
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?
Click to expand...

John Now Im getting
Compile Error
Sub or Function not defined
Regards Bob
 
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?
Click to expand...

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
 
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]
Click to expand...

Please post the query SQL.
 
John Now Im getting
Compile Error
Sub or Function not defined
Regards Bob
Click to expand...

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.
 
John W. Vinson said:
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]
Click to expand...

Please post the query SQL.
Click to expand...

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,
tblOwnerInfo.OwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfo.OwnerID;
 
John W. Vinson said:
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]
Click to expand...

Please post the query SQL.
Click to expand...

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,
tblOwnerInfo.OwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfo.OwnerID;
Click to expand...

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.
 
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!


John W. Vinson said:
John W. Vinson said:
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.
Click to expand...

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,
tblOwnerInfo.OwnerLastName
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID =
tblOwnerInfo.OwnerID;
Click to expand...

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.
Click to expand...
 
Bob Vance said:
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!
Click to expand...
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 said:
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!
Click to expand...

***OOPS bottom table should read tblHorseInfo not tblHorseDetails****
 
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
Click to expand...

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