Query

  • Thread starter Thread starter Dale Brown
  • Start date Start date
D

Dale Brown

I have a query that works in my mdb (Access 2000) but when I convert it
to an mde the query does not return the right values. Any ideas
 
I have a query that works in my mdb (Access 2000) but when I convert it
to an mde the query does not return the right values. Any ideas
Click to expand...

Please post the SQL view of the query.
 
John,

The following is the sql view of the query. The highpointhorsecompeted
is a query itself. Classinfosel is a table. The
highpointhorsecompeted.competed column is a subselect in he
highpointhorsecompeted query. In the mdb version this query works fine
in the mde version he competed column comes back with zeroes. I have
included the highpointhorsecompeted query below the first one. Let me
know what you think.

Dale

SELECT HighPointHorseCompeted.HorseNbr, HighPointHorseCompeted.HorseID,
Sum(IIf(highpointhorsecompeted.competed>=9 And
highpointhorsecompeted.placing<=9 And
highpointhorsecompeted.placing>0,10-highpointhorsecompeted.placing,IIf(highpointhorsecompeted.placing<9
And
highpointhorsecompeted.placing>0,highpointhorsecompeted.competed+1-highpointhorsecompeted.placing,0)))
AS pts, HighPointHorseCompeted.HorseName, HighPointHorseCompeted.BreedName
FROM HighPointHorseCompeted INNER JOIN ClassInfoSel ON
HighPointHorseCompeted.classid=ClassInfoSel.classid
WHERE classinfosel.code=0
GROUP BY HighPointHorseCompeted.HorseNbr,
HighPointHorseCompeted.HorseID, HighPointHorseCompeted.HorseName,
HighPointHorseCompeted.BreedName
ORDER BY Sum(IIf(highpointhorsecompeted.competed>=9 And
highpointhorsecompeted.placing<=9 And
highpointhorsecompeted.placing>0,10-highpointhorsecompeted.placing,IIf(highpointhorsecompeted.placing<9
And
highpointhorsecompeted.placing>0,highpointhorsecompeted.competed+1-highpointhorsecompeted.placing,0)))
DESC;


SELECT EbEntryPlace.EntryBlankID, EBHorse.HorseID, EBHorse.HorseNbr,
ClassInfo.ClassNumber, EbEntryPlace.Placing, (select count(*) from
ebentry ebentry_4 where ebentry_4.classid = classinfo.classid and
(ebentry_4.status = 'A' or ebentry_4.status = 'E')) AS competed,
Person.FirstName & " " & Person.LastName AS rider, Horse.HorseName,
Breed.BreedName, ClassInfo.ClassID
FROM (activeShow INNER JOIN eb ON activeShow.ShowID=eb.ShowID) INNER
JOIN ((Horse INNER JOIN Breed ON Horse.BreedID=Breed.BreedID) INNER JOIN
(ClassInfo INNER JOIN (EBHorse INNER JOIN (Person INNER JOIN
EbEntryPlace ON Person.ID=EbEntryPlace.RiderID) ON
(EBHorse.EntryBlankID=EbEntryPlace.EntryBlankID) AND
(EBHorse.HorseID=EbEntryPlace.HorseID)) ON
ClassInfo.ClassID=EbEntryPlace.ClassID) ON
(Horse.HorseID=EBHorse.HorseID) AND
(Horse.HorseID=EbEntryPlace.HorseID)) ON
eb.EntryBlankID=EbEntryPlace.EntryBlankID
ORDER BY EBHorse.HorseNbr, ClassInfo.ClassNumber;
 
John,

I posted this back to the newsgroup and never got an answer. Can you
take a look at this and let me know what you think. Thanks for any help
you can provide.

Dale

John,

The following is the sql view of the query. The highpointhorsecompeted
is a query itself. Classinfosel is a table. The
highpointhorsecompeted.competed column is a subselect in he
highpointhorsecompeted query. In the mdb version this query works fine
in the mde version he competed column comes back with zeroes. I have
included the highpointhorsecompeted query below the first one. Let me
know what you think.

Dale

SELECT HighPointHorseCompeted.HorseNbr, HighPointHorseCompeted.HorseID,
Sum(IIf(highpointhorsecompeted.competed>=9 And
highpointhorsecompeted.placing<=9 And
highpointhorsecompeted.placing>0,10-highpointhorsecompeted.placing,IIf(highpointhorsecompeted.placing<9
And
highpointhorsecompeted.placing>0,highpointhorsecompeted.competed+1-highpointhorsecompeted.placing,0)))
AS pts, HighPointHorseCompeted.HorseName, HighPointHorseCompeted.BreedName
FROM HighPointHorseCompeted INNER JOIN ClassInfoSel ON
HighPointHorseCompeted.classid=ClassInfoSel.classid
WHERE classinfosel.code=0
GROUP BY HighPointHorseCompeted.HorseNbr,
HighPointHorseCompeted.HorseID, HighPointHorseCompeted.HorseName,
HighPointHorseCompeted.BreedName
ORDER BY Sum(IIf(highpointhorsecompeted.competed>=9 And
highpointhorsecompeted.placing<=9 And
highpointhorsecompeted.placing>0,10-highpointhorsecompeted.placing,IIf(highpointhorsecompeted.placing<9
And
highpointhorsecompeted.placing>0,highpointhorsecompeted.competed+1-highpointhorsecompeted.placing,0)))
DESC;


SELECT EbEntryPlace.EntryBlankID, EBHorse.HorseID, EBHorse.HorseNbr,
ClassInfo.ClassNumber, EbEntryPlace.Placing, (select count(*) from
ebentry ebentry_4 where ebentry_4.classid = classinfo.classid and
(ebentry_4.status = 'A' or ebentry_4.status = 'E')) AS competed,
Person.FirstName & " " & Person.LastName AS rider, Horse.HorseName,
Breed.BreedName, ClassInfo.ClassID
FROM (activeShow INNER JOIN eb ON activeShow.ShowID=eb.ShowID) INNER
JOIN ((Horse INNER JOIN Breed ON Horse.BreedID=Breed.BreedID) INNER JOIN
(ClassInfo INNER JOIN (EBHorse INNER JOIN (Person INNER JOIN
EbEntryPlace ON Person.ID=EbEntryPlace.RiderID) ON
(EBHorse.EntryBlankID=EbEntryPlace.EntryBlankID) AND
(EBHorse.HorseID=EbEntryPlace.HorseID)) ON
ClassInfo.ClassID=EbEntryPlace.ClassID) ON
(Horse.HorseID=EBHorse.HorseID) AND
(Horse.HorseID=EbEntryPlace.HorseID)) ON
eb.EntryBlankID=EbEntryPlace.EntryBlankID
ORDER BY EBHorse.HorseNbr, ClassInfo.ClassNumber;


it to an mde the query does not return the right values. Any ideas
Please post the SQL view of the query.

John W. Vinson[MVP] Come for live chats
Click to expand...
every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
 
Back
Top