Joins Problem 2

[bxgti4x4]

I have two tables, TblCompetitor and TblClass/Peters.

TblCompetitor has 93 records, one for each competitor for the current year. TblClass/Peters lists all competitors for the past four years, thus many of the names have an entry for more than one year.

Using the names and initials from TblCompetitor (to eliminate two people with the same name) I want to extract all records for each name which appear in TblsClass/Peters.

E.g. Draper J. is competing in the current year but has also competed in 2002, 2003 and 2004. Therefore, the query should return three records from TblClass/Peters against the name and initials in TblCompetitor

I have created the following query but, no matter how I adjust the joins, I cannot retrieve more than one record from TblClass/Peters for each name in TblCompetitor.

SELECT TblCompetitor.TxtName, TblCompetitor.TxtInitials, [TblClass/Peters].TxtName, [TblClass/Peters].TxtInitials, [TblClass/Peters].TxtRank_Rating, [TblClass/Peters].TxtUnit, [TblClass/Peters].TxtClass, [TblClass/Peters].fPetersPrize, [TblClass/Peters].fWonPeters, [TblClass/Peters].fISTeam, [TblClass/Peters].TxtYear
FROM TblCompetitor LEFT JOIN [TblClass/Peters] ON (TblCompetitor.TxtName = [TblClass/Peters].TxtName) AND (TblCompetitor.TxtInitials = [TblClass/Peters].TxtInitials)
WHERE (((TblCompetitor.TxtName) Is Not Null))
ORDER BY TblCompetitor.TxtName;

Can anyone please advise me where I am going wrong.

Best Regards
John

 

[lespaul]

This should get what you are looking for. This will return all the records from [TblClass/Peters] where the person also exists in TblCompetitor and the name & initials match and then retrieves the current year information in the UNION clause:

SELECT [TblClass/Peters].TxtName, [TblClass/Peters].TxtInitials, [TblClass/Peters].TxtRank_Rating, [TblClass/Peters].TxtUnit, [TblClass/Peters].TxtClass, [TblClass/Peters].fPetersPrize, [TblClass/Peters].fWonPeters, [TblClass/Peters].fISTeam, [TblClass/Peters].TxtYear
FROM [TblClass/Peters] INNER JOIN TblCompetitor ON ([TblClass/Peters].TxtName = TblCompetitor.TxtName) AND (TblClass/Peters].TxtInitials = TblCompetitor.[TxtInitials)
UNION
SELECT TblCompetitor.TxtName, TblCompetitor.TxtInitials, "", "", "", "", "", "", "", "", "2005"
FROM TblCompetitor 
ORDER BY TxtName, TxtYear

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[cmmrfrds]

What do you get when doing an equal join.

FROM TblCompetitor INNER JOIN [TblClass/Peters] ON (TblCompetitor.TxtName = [TblClass/Peters].TxtName) AND (TblCompetitor.TxtInitials = [TblClass/Peters].TxtInitials)
ORDER BY TblCompetitor.TxtName;

Since by adding this where clause to the left join it is basically doing the same as an equal join.
WHERE (((TblCompetitor.TxtName) Is Not Null))

If you want the unequals then
WHERE (((TblCompetitor.TxtName) Is Null))

If you want both then
WHERE (TblCompetitor.TxtName Is Null or TblCompetitor.TxtName > '')

 

[bxgti4x4]

Thanks Leslie and cmmrfrds,

Leslie, I keep getting the error message "Syntax Error" when I try to run the query. I have checked through but I cannot see where the problem is. Could you please check the code for me.

cmmrfrds

The reason for the WHERE clause is to remove blank records. Each team has been allocated a set of index numbers but not all of them are used. I'll try what you suggest

Best Regards
John

 

[bxgti4x4]

Leslie,

I constructed your code from scratch and got rid of the syntax error warning. However, when I ran the query, it still did not extract all the details from TblClass/Peters for each of the competitors in TblCompetitor.

My code ended up as follows:

SELECT [TblClass/Peters].TxtName, [TblClass/Peters].TxtInitials, [TblClass/Peters].TxtRank_Rating, [TblClass/Peters].TxtUnit, [TblClass/Peters].TxtClass, [TblClass/Peters].fPetersPrize, [TblClass/Peters].fWonPeters, [TblClass/Peters].fISTeam, [TblClass/Peters].TxtYear
FROM [TblClass/Peters] INNER JOIN TblCompetitor ON ([TblClass/Peters].TxtInitials = TblCompetitor.TxtInitials) AND ([TblClass/Peters].TxtName = TblCompetitor.TxtName)
UNION SELECT TblCompetitor.TxtName, TblCompetitor.TxtInitials, TblCompetitor.TxtRank_Rating, TblCompetitor.TxtUnit, TblCompetitor.TxtClass, TblCompetitor.fPetersPrize,TblCompetitor.fWonPeters, TblCompetitor.fISTeam, TblCompetitor.TxtYear
FROM TblCompetitor
ORDER BY TxtName, TxtYear;

Any ideas?

Best Regards
John

 

[lespaul]

what happens if you just run the first query? Do you get the ones you are expecting? Can you show me an example of some data from your query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[bxgti4x4]

Leslie,

I played around with the query for a bit longer and, by accident, cut out the join on TxtInitials. After that, it worked.

I need to make a few checks before I am certain but it certainly looks like the Initials were confusing the issue - probably because some were entered in upper case, some in lower case, some with full stops and some without.

I'll respond again when I have checked.

Thanks for your help - have a star.

Best Regards
John

 

[lespaul]

the information in TxtInitials has to be IDENTICAL in each table in order for the join to work. Glad you're making progress!

 

[jsteph]

You could call Dr. Ruth, that might help.

Oh...you said Joins problem, not Loins problem...Never mind.
--Emily Latella

 

[bxgti4x4]

Leslie,

Having changed the data in the TblClass/Peters to match that in TblCompetitor, everything worked fine. Also I can now understand the use of the Union part of the query. The final report gives me just what I wanted.

Many thanks for your help.

Best Regards
John

 

[lespaul]

no problem, glad it's working!

leslie