More querying of null values... (1 Viewer)

Darrell

Registered User.
Local time
Today, 11:17
Joined
Feb 1, 2001
Messages
299
Hi guys,
I have an issue with my soccer db which has happened for the first time ever and it has got me stumped.

When I report the weekends game in my db, I enter the results for the division into the main form and when my team is selected, I enter the goal scorers name and the number of goals in a subform. I also enter in the MVP points into another subform.

In a query I have the results for our team and I have wedged in the goal scorers and the MVP into this also. This means that the same data appears in the query for each goal scorer but that is ok as I just do a hide duplicate values in the report and it all looks fine.

The trouble is that last week, we failed to score a goal for the first time in over two years, and therefore there is a null value in the scorers table for that record and so my query is not reporting anything.

Can anyone shed some light on what I should try?

dh
 

ColinEssex

Old registered user
Local time
Today, 11:17
Joined
Feb 22, 2002
Messages
9,110
You could put something in the field like "N/A" or "None" - it seems its not going to happen very often, unless this the beginning of a slump!!!

Col
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:17
Joined
Feb 19, 2002
Messages
42,970
Since the MVP and the Goal Scorers are not related to each other, they should not be included in the same query. Your report should be set up the same way as your form. A main report with two subreports.

Changing the join from inner to left MAY fix your query so you can continue as you are. However, if for some reason there are ever 2 or more goals scored and 2 or more MVP's for the same game, your current process will NOT work.
 

Darrell

Registered User.
Local time
Today, 11:17
Joined
Feb 1, 2001
Messages
299
OK thanks Pat, I have now created the report with two sub reports in it and it works fine. :)

On a sort of related topic, I have another report which shows the number of goals scored by a player in the three competitions that we are in and totals them also. I have to admit that as this was the very first db I ever created, I had no idea of how to get the query to show a record for a player in one competition if he had no record in another and so I just assigned a zero to everyone who hadn't scored in the first record of each of the three comps.

This has worked fine but I'm now at the point where I want to do this properly and unfortunately, I still don't know how to do it :confused:

any idea??

thanks

dh
 

Darrell

Registered User.
Local time
Today, 11:17
Joined
Feb 1, 2001
Messages
299
Hmmm... I don't know if this was the correct method or not but it seemed to have worked so what the heck....

1. I created a union query to get all of the goal scorers names in one place. (Never done these before so that was fun....)

2. I created a select query based on this union query and the goal scorers table for each of the competitions and did a IIf(IsNull([No of Goals]),"0",[No of Goals]) in each one.

3. I created another query based on these three queries which shows the number of goals for each competition by scorer and totals them also.

4. Report is now based on this query and it works and I am a happy chappy. :)

dh
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:17
Joined
Feb 19, 2002
Messages
42,970
When you want to join two tables and one of them might not have any "transactions", use a left join rather than an inner join. You can change the join type by clicking on the center of the join line. You will see three options. Choose the one (left or right) that makes sense for your relationship.
 

Darrell

Registered User.
Local time
Today, 11:17
Joined
Feb 1, 2001
Messages
299
What I have realised after reading your post is that I was doing this all wrong from the start. Instead of joining the players table to the goal scorers table for each competition in the manner you suggested and then joining them together, I was trying to mash all three of the goal scorers tables together on their own and of course it didn't work.

Oh well, at least I have an insight as to how to do union queries now....

Thanks again Pat. :)

dh
 

Users who are viewing this thread

Top Bottom