Query returns results from one table/query but not from other table/query

[steveflanuk]

Advanced apologies for the long question.

I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)

I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.

The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.

We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.

For example:

Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result.
Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc
Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc

I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):

MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.

I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.

However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.

Can anyone figure out what I'm doing wrong?

Many thanks in advance

 

[neileg]

I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):

Mmm... Why have you created separate queries? Are you including all 10 queries in your second query?

Without seeing these queries in more detail it's hard to advise. Can you post the database or at least the SQL of the second query.

 

[steveflanuk]

I would do, but the database is on a computer at the club, so I can't access it at the moment.

Basically, I tried to do a crosstab query but it wouldn't allow me to provide the player and result for each individual rink (although it is highly likely I did something wrong!).

Therefore, my assumption was that if I produced a sub-query for each rink indidividually to provide the player and score for that rink, then I should be able to pull all of these in to another, overall, query to give me the information.

From what I remember, the sub-query for each rink was based on tblMembers and tblMatchDetails and provided the following:

Field1: MatchID [from tblMatchDetails] - Grouped By field
Field2: Name [from tblMembers based on MemID from tblMatchDetails joined with MemID from tblMembers] - Grouped By field
Field3: Rink [from tblMatchDetails which was equal to the rink value entered in the criteria - e.g. Rink 1 = 1 in the criteria field] - Where field

I called this qryRinkOnePlayers.

The results are exactly as I would expect, with the correct information being shown.

I tried the overall query with all the rink queries attached, but when that didn't work I tried it with just 1 rink query first.

The overall query was based on tblMatches and qryRinkOnePlayers.

Field1: Date [from tblMatches] - Grouped By field
Field2: Opponents [from tblMatches] - Grouped By field
Field3: ScoreFor [from tblMatches] - Grouped By field
Field4: ScoreOpp [from tblMatches] - Grouped By field
Field5: Rink1Player [from qryRinkOnePlayers] - Grouped By field
Field6: Rink1Score [from qryRinkOnePlayers] - Grouped By field

The table and query were linked by the field MatchID, and the join properties was to includes all records from tblMatches and only records from tblRinkOnePlayers where the field matched.

The records returned showed the match information (from tblMatches) as I would have expected, but the rink information fields (from qryRinkOnePlayers) was entirely blank.

I then changed the join properties to include all records from qryRinkOnePlayers and only records from tblMatches where the joined fields were equal.

This time the records showed the Player information from the query but all the information from tblMatches was blank!

I then tried the "only records where both fields are equal" join, and I got nothing whatsoever.

However, on reflection I am now wondering whether the MatchID field in the table tblMatchDetails may have been text rather than numeric - would this be the possible cause?

 

[neileg]

However, on reflection I am now wondering whether the MatchID field in the table tblMatchDetails may have been text rather than numeric - would this be the possible cause?

Definitely would stop things working! However I would have expected Access to flag up a data type mismatch.