Custom field in a query and null to 0

[ITguy1981]

I've been trying to help my sister with a database query. I need the query to count the records of a field and display a number for the records of the field. For instance, one field is [Genre] and the other is [Show]. The query needs to list the Genres along with the number of shows for each genre. I've been able to just use the query design and add the genre field and I can add the show field, use totals count which gives me the genre counts the number of shows. My problem is the null. Some genres don't have a show listed so the genre doesn't even show up in the result. If I could get the the genres that have null shows to result a 0 it would be perfect.

 

[Galaxiom]

Derive a field using this in the designer field cell:

NewName:Nz([fieldname],0)

Then do totals on that field instead.

 

[ITguy1981]

Thanks for the quick reply. That works to an extent. When I do that I still get the same result. Say I have field genres and the field shows. Two genres don't have any record information. The query is also across two tables. One table just has the different types of genres along with an ID for a primary key. The other table has the show, viewers, etc. I need the query to list all 6 genres along with how many shows are in those categories. Even though I used the nz it still only lists 4 genres with the count of the shows for each. The two genres that have no records attached to show, but I would like them listed with a show count of 0. Whether I use Number of Shows: Nz([Show],0), or choose the actual show field and do a cound I get the same result.

 

[Galaxiom]

The query is also across two tables.

That is an essential piece of information.

You need to use an outer join in the query. Right click the Join and edit the join properties.

 

[ITguy1981]

There is a one to many join between the two tables. The table that has a ID for primary key and genres is joined by a one to many relationship from ID to genres in the other table (the many). Still not including genres that have no data in the other table. However, I guess it can't do a null to 0 because if there is record for a show of the other two genres then the field doesn't exist as null to make 0. FML I'm stupid. Thanks for you help though. :banghead:

 

[Galaxiom]

A one-to-many relationship is not the same as an outer join.

Unless you change the join type in the query, only those with records in both tables will be returned.