Query not returning all data 2

[rj51cxa]

I have two tables - TblCompetitor and TblWinners. TblCompetitor contains details for competitors for the current year and TblWinners contains details of competitors from previous years such as the class in which they were competing and details of the prizes they had won.

I have written the following query in order to compare the names of current competitors with the names in the Winners table to ensure that they have not been entered for competitions for which they are no longer eligable, or that they have been entered in the wrong class.

 SELECT TblCompetitor.TxtUnit, TblCompetitor.TxtName, TblCompetitor.TxtInitials, TblCompetitor.TxtServiceNumber, TblCompetitor.TxtRank_Rating, TblCompetitor.TxtClass, TblWinners.TxtClass AS [Previous Class], TblWinners.fPetersPrize, TblWinners.fWonPeters AS Peters, TblWinners.fWrnWinner AS Mersey, TblWinners.fISTeam AS [IS TEAM], TblWinners.fWonTyne AS Tyne, TblWinners.fWonTurtle AS Turtle, Right([BisleyYear] & '',4) AS [Year]
FROM TblCompetitor INNER JOIN TblWinners ON TblCompetitor.TxtName = TblWinners.TxtName
WHERE (((TblCompetitor.TxtName) Is Not Null) AND ((TblWinners.fWonPeters)=True)) OR (((TblWinners.fWrnWinner)=True)) OR (((TblWinners.fISTeam)=True)) OR (((TblWinners.fWonTyne)=True)) OR (((TblWinners.fWonTurtle)=True))
ORDER BY TblCompetitor.TxtUnit, TblCompetitor.TxtName;

The problem is that, whilst this code extracts the names of previous winners from TblWinners, it is not extracting the data for each year. ie. for an entry in TblCompetitor there should be up to four records in TblWinners. The code is extracting two records for one competitor, where there should be four, and one record for another competitor, where there should be five. It is not reliable.

I suspect I am using a Select query where I should be using something else but I would appreciate any advice.

Best Regards
John

 

[r937]

it's gotta be your ANDs and ORs not being evaluated correctly because of all the stupid *$*#^* nested parentheses

here's your WHERE clause --

 WHERE (
       (
       (TblCompetitor.TxtName) Is Not Null
       )
   AND (
       (TblWinners.fWonPeters)=True
       )
       ) 
    OR (
       (
       (TblWinners.fWrnWinner)=True
       )
       ) 
    OR (
       (
       (TblWinners.fISTeam)=True
       )
       ) 
    OR (
       (
       (TblWinners.fWonTyne)=True
       )
       ) 
    OR (
       (
       (TblWinners.fWonTurtle)=True
       )
       )

and here's what i think it should be --

 WHERE TblCompetitor.TxtName Is Not Null
   AND (
       TblWinners.fWonPeters = True
    OR TblWinners.fWrnWinner = True
    OR TblWinners.fISTeam = True
    OR TblWinners.fWonTyne = True
    OR TblWinners.fWonTurtle = True
       )

r937.com | rudy.ca

 

[PHV]

Another WHERE clause you may try:
WHERE TblCompetitor.TxtName Is Not Null
AND True In (TblWinners.fWonPeters, TblWinners.fWrnWinner, TblWinners.fISTeam, TblWinners.fWonTyne, TblWinners.fWonTurtle)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rj51cxa]

Thanks r937.com and PHV for your replies.

Unfortunately neither of your suggestions produced a result. However, I had an idea and decided to try a more simplified code by taking out all references to TblWinners except [TblWinners]![TxtClass] AS [Previous Class] and[BisleyYear] & '',4) AS [Year] as follows:

SELECT DISTINCT TblCompetitor.TxtUnit, TblCompetitor.TxtName, TblCompetitor.TxtInitials, TblCompetitor.TxtServiceNumber, TblCompetitor.TxtRank_Rating, TblCompetitor.TxtClass, Right([BisleyYear] & '',4) AS [Year], [TblWinners]![TxtClass] AS [Previous Class]
FROM TblCompetitor INNER JOIN TblWinners ON TblCompetitor.TxtName = TblWinners.TxtName
WHERE (((TblCompetitor.TxtName) Is Not Null))
ORDER BY TblCompetitor.TxtUnit, TblCompetitor.TxtName, Right([BisleyYear] & '',4);

This code now gives me all entries from Tbls Winners which refer to TxtName in TblCompetitor BUT in a few cases, I get a duplicate entry because of [TblWinners]![TxtClass] AS [Previous Class]. If I take this out, I get the correct number of entries for each competitor.

The problem seems to be in the connection between the tables. As soon as you starting adding more than one field from TblWinners, it all goes wrong.

Any ideas would be most welcome

Best Regards
John

 

[rj51cxa]

r937.com and PHV

I've managed to sort out part of the problem, using the original code. I realised my selection criteria were not explicit enough so I joined the two tables using both txtname and txtinitials. This has removed the odd duplication effects I mentioned before. If I then take out

AND True In (TblWinners.fWonPeters, TblWinners.fWrnWinner, TblWinners.fISTeam, TblWinners.fWonTyne, TblWinners.fWonTurtle)

as suggested by PHV, I can now see the details for each competitor, for every year they have competed, and whether or not they have won in any of the competitions.

PHV's code worked but the results only showed the years in which a competitor won a competition, which was removing quite a lot of useful data.

Looking at the results of the query as it now is, I think that I am getting more information than I thought I could get before, so I will leave it there.

Thanks to both of you for your help.
Best Regards
John