Displaying categories with null values in a query. (1 Viewer)

[amiscus]

I have to list sales by divison and for a period from a table that includes all prospective sales successful or otherwise. So I have designed a query to display the divison name and then the sums and counts for sales in the division. I then have 2 "where" functions to get the correct time period and to only include sales that were succesful for these totals. However it only returns back 9 divisions. There are 14 divisons total but 5 have not had any succesful sales for the time period. Is there a way so it displays these divisons with no succesful sales in the results and just display zeroes for the sum and count? Thanks in advance for any help.

 

[MSAccessRookie]

I have to list sales by divison and for a period from a table that includes all prospective sales successful or otherwise. So I have designed a query to display the divison name and then the sums and counts for sales in the division. I then have 2 "where" functions to get the correct time period and to only include sales that were succesful for these totals. However it only returns back 9 divisions. There are 14 divisons total but 5 have not had any succesful sales for the time period. Is there a way so it displays these divisons with no succesful sales in the results and just display zeroes for the sum and count? Thanks in advance for any help.

If you use the nvl(SalesAmount,0) function in the query, then all Null Sales will be calculated as 0 and the Divisions without sales willhave a total sales of $0.

 

[neileg]

Rookie, not sure what nvl() is? Do you mean Nz()?

In any event, the better way to do this is to join the data back to a list of all divisions. The default join in Access is an inner join which only returns data where there is a match on both sides of the join. Use a left or right join (type 2 or 3 in Access speak) whichever is appropriate and you will get your 14 reports.

 

[MSAccessRookie]

Rookie, not sure what nvl() is? Do you mean Nz()?

In any event, the better way to do this is to join the data back to a list of all divisions. The default join in Access is an inner join which only returns data where there is a match on both sides of the join. Use a left or right join (type 2 or 3 in Access speak) whichever is appropriate and you will get your 14 reports.

Yes, I meant Nz(). I have worked with several versions of SQL, and I get them mixed up sometimes. In any event, the Left/Right Join idea is probably a better aproach, and the Op should try that.

 

[amiscus]

Sorry this reply is so late but neileg your advice worked perfectly. Thanks again.