Help With Query (DB attached)

[copegjc15]

Okay, I have no clue what is wrong. I am really a novice at this stuff.

I have a query:

qry(REC-P)Game-MostStrikeouts

That works properly. However, when I add a 2nd table to grab info for the query which is named: qry(REC-P)Game-MostKs

I don't get the same data as in the 1st query listed above, and I should.

Anybody have any ideas????

 

[ck1999]

Most likely has to do with the innerjoin

sql for strikeout query
SELECT TOP 5 [qry(P)Pitching].GameDate, [qry(P)Pitching].PlayerName, [qry(P)Pitching].K, [qry(P)Pitching].Opp
FROM [qry(P)Pitching]
ORDER BY [qry(P)Pitching].K DESC;

'sql for Ks query
SELECT TOP 5 [qry(P)Pitching].GameDate, [qry(P)Pitching].PlayerName, [qry(P)Pitching].K, "vs. " & [tblISBLTeams]![Name] & " " & [tblISBLTeams]![NickName] AS Expr2
FROM tblISBLTeams INNER JOIN [qry(P)Pitching] ON (tblISBLTeams.Year = [qry(P)Pitching].Season) AND (tblISBLTeams.Team = [qry(P)Pitching].Opp)
ORDER BY [qry(P)Pitching].K DESC;

Since you are using an inner join you are changing your underlining data record

The inner join cuts down records from 3586 to 494

ck1999

 

[ck1999]

Just a followup

Your K's query was only joining for 2007 and that had be very puzzled and that is why there were only 494 entries. So I go to looking your tblpitching has opponents with leading spaces so try this

Select top 5 [qry(P)Pitching].GameDate, [qry(P)Pitching].PlayerName, [qry(P)Pitching].K, "vs. " & tblISBLTeams!Name & " " & tblISBLTeams!NickName AS Expr2
FROM tblISBLTeams INNER JOIN [qry(P)Pitching] ON (tblISBLTeams.Year =[qry(P)Pitching].Season) AND (tblISBLTeams.Team =ltrim( [qry(P)Pitching].Opp))
ORDER BY [qry(P)Pitching].K DESC;

the Ltrim deletes leading spaces. However I would recommend going into tblpitching and fixing the data (by deleting preceding spaces) that way this error does not happen again when trying to compare the two tables

ck1999

 

[copegjc15]

THANKS A LOT!!!

Good catch on the leading spaces. I was banging my head on my desk for so long trying to figure this out. Haha.

I didn't even think to check for that, I was looking at the formatting of each field in that table to see if something was different. But all the formats were the same.

Thanks again.