Report that Shows "null" value

[ionmoon]

Grrr. Okay. I need a report that would show say, sales for a particular time period. Now, I have several employees. Some will have sales in a given month and others will not.

I did a query that shows a count of sales. For each person who has at least one sale, it list how many they have had. What I NEED is a way to create a report which will show a list of salesperson name and number of sales, including those with none.

Thanks
Tara

 

[Norie]

Tara

You should be able to to that in the query, and if the report is based on that then you shouldn't need to change anything.

Can you post details of the query?

 

[ionmoon]

It's a little more complicated then the original post, but trying to keep it as simple & anonymous as possible.

Okay so I have:
table1 with the saleperson and id number
table2 with id number and "sale type" and date of sale
query1: (from table 2) id number, "sale type," visit date [enter start and end dates]
query2: (from table1 and query1) name(t1) & [COUNT] sale type(q1)


Only the people who have had a sale show up on Query2. Is there a way to list EVERY sales person and number of sales (even if it is zero)

 

[Norie]

So the salespersons in table1 won't have any records in table2?

Let's say the tables are like this:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblSalesPeople</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>SalesPerson</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1 align=right>1</TD><TD borderColor=#eeece1>Mike</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>2</TD><TD borderColor=#eeece1>Jennifer</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>3</TD><TD borderColor=#eeece1>Frances</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>4</TD><TD borderColor=#eeece1>Robert</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>5</TD><TD borderColor=#eeece1>Stuart</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>6</TD><TD borderColor=#eeece1>Mary</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>tblSales</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>SaleType</TH><TH bgColor=#c0c0c0 borderColor=#000000>SaleDate</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1 align=right>1</TD><TD borderColor=#eeece1>Internet</TD><TD borderColor=#eeece1 align=right>04/05/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>1</TD><TD borderColor=#eeece1>Phone</TD><TD borderColor=#eeece1 align=right>01/05/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>2</TD><TD borderColor=#eeece1>Personal</TD><TD borderColor=#eeece1 align=right>10/05/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>2</TD><TD borderColor=#eeece1>Phone</TD><TD borderColor=#eeece1 align=right>16/05/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>3</TD><TD borderColor=#eeece1>Personal</TD><TD borderColor=#eeece1 align=right>11/05/2011</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>4</TD><TD borderColor=#eeece1>Internet</TD><TD borderColor=#eeece1 align=right>11/05/2011</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

This is the SQL for a query that will return a count of Sales for each salesperson.

SELECT tblSalesPeople.ID, tblSalesPeople.SalesPerson, Count(tblSales.SaleType) AS CountOfSaleType
FROM tblSales RIGHT JOIN tblSalesPeople ON tblSales.ID = tblSalesPeople.ID
GROUP BY tblSalesPeople.ID, tblSalesPeople.SalesPerson;


<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>CountOfSales</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>SalesPerson</TH><TH bgColor=#c0c0c0 borderColor=#000000>CountOfSaleType</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>Mike</TD><TD borderColor=#d0d7e5 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5>Jennifer</TD><TD borderColor=#d0d7e5 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3</TD><TD borderColor=#d0d7e5>Frances</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4</TD><TD borderColor=#d0d7e5>Robert</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5>Stuart</TD><TD borderColor=#d0d7e5 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>6</TD><TD borderColor=#d0d7e5>Mary</TD><TD borderColor=#d0d7e5 align=right>0</TD></TR></TBODY><TFOOT></TFOOT></TABLE>


This SQL will return a count of sales grouped by sale type.

SELECT tblSalesPeople.ID, tblSalesPeople.SalesPerson, tblSales.SaleType, Count(tblSales.SaleDate) AS CountOfSaleDate
FROM tblSales RIGHT JOIN tblSalesPeople ON tblSales.ID = tblSalesPeople.ID
GROUP BY tblSalesPeople.ID, tblSalesPeople.SalesPerson, tblSales.SaleType;

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>CountOfSalesByType</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>SalesPerson</TH><TH bgColor=#c0c0c0 borderColor=#000000>SaleType</TH><TH bgColor=#c0c0c0 borderColor=#000000>CountOfSaleDate</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>Mike</TD><TD borderColor=#d0d7e5>Internet</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1</TD><TD borderColor=#d0d7e5>Mike</TD><TD borderColor=#d0d7e5>Phone</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5>Jennifer</TD><TD borderColor=#d0d7e5>Personal</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2</TD><TD borderColor=#d0d7e5>Jennifer</TD><TD borderColor=#d0d7e5>Phone</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3</TD><TD borderColor=#d0d7e5>Frances</TD><TD borderColor=#d0d7e5>Personal</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4</TD><TD borderColor=#d0d7e5>Robert</TD><TD borderColor=#d0d7e5>Internet</TD><TD borderColor=#d0d7e5 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>5</TD><TD borderColor=#d0d7e5>Stuart</TD><TD borderColor=#d0d7e5>

</TD><TD borderColor=#d0d7e5 align=right>0</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>6</TD><TD borderColor=#d0d7e5>Mary</TD><TD borderColor=#d0d7e5>

</TD><TD borderColor=#d0d7e5 align=right>0</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

 

[ionmoon]

Norie- Wow. Thanks for the detailed post. I will try that out and see how it goes.

Tara