Query Expression Help!
in [General]
|
Prev: hidding group commands in ribbon and setting password with vb
Next: Running A2003 and A2007 on same machine
From: John W. Vinson on 2 May 2010 19:59 On Mon, 3 May 2010 11:11:35 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: > >"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message >news:ukprt5ltrdnogu6adgdeup3bul5ofmqov9(a)4ax.com... >> On Sun, 2 May 2010 17:37:18 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: >> >>>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 [MVP] > >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; > 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. -- John W. Vinson JVinson *at* Wysard Of Info *dot* com -- John W. Vinson [MVP]
From: Bob Vance on 2 May 2010 20:28 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" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:074st5tlhmcreg271cjm26tblc7tabt1s7(a)4ax.com... > On Mon, 3 May 2010 11:11:35 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: > >> >>"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message >>news:ukprt5ltrdnogu6adgdeup3bul5ofmqov9(a)4ax.com... >>> On Sun, 2 May 2010 17:37:18 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> >>> wrote: >>> >>>>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 [MVP] >> >>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; >> > > 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. > -- > > John W. Vinson JVinson *at* Wysard Of Info *dot* com > -- > > John W. Vinson [MVP]
From: Bob Vance on 2 May 2010 20:32 "Bob Vance" <rjvance(a)ihug.co.nz> wrote in message news:uFW0odl6KHA.5016(a)TK2MSFTNGP02.phx.gbl... > 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
From: Bob Vance on 2 May 2010 20:34 "Bob Vance" <rjvance(a)ihug.co.nz> wrote in message news:uFW0odl6KHA.5016(a)TK2MSFTNGP02.phx.gbl... > 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****
From: John W. Vinson on 2 May 2010 21:18
On Mon, 3 May 2010 12:28:01 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: >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; -- John W. Vinson [MVP] |