Help supressing data on report

[smalley]

I am looking for a way to write a query and display results on a report. Here are the tables and fields:
REGION SELLERS SALES
name seller_id date
branch branch seller_id
sellers_nm sale_type
amount
received(yes/no) - yes/no data type

region and sellers are joined on branch and sellers and sales are joined on seller_id.

I need to display the report as follows:
Region Name
Sale_Type # Required ____ # Received ____

The report is grouped by region and I only need the number required and the number received. I get the number required by counting the number of sellers for each sale_type. For instance, if I have a sale type "Cold Calls" and I have 4 sellers assigned to "Cold Calls" then 4 are required for that region. I get this information from the Sales table. Each seller will have a yes or no for each sale type and I need to know how many have a yes. The part that I am having problems with is getting the number of yes's. I have my report made and I have the total required and I can display the number of yes's and the number of no's but I don't know how to get rid of the no's.
Please help.

 

[JoeMiller]

Sounds like you COULD use a DCount for this process, there's not enough information for me to accurately gauge this and I don't know how you've setup you're report, table, etc... Set an unbound control's control source to something like this:

=DCount("*","MyTableName","[MyField]='Yes' AND [MySeller]=[MySeller]&quot

The first MySeller is the field name for the Seller ID number. The second would be the name of the control on the report that holds the id number for the seller. The above equation also assumes the MyField (your field with Yes/No) is a text field holding "Yes" or "No." IF you used an actual Yes/No datatype field, then you would change the statement to this:

=DCount("*","MyTableName","[MyField]=-1 AND [MySeller]=[MySeller]&quot

HTH Joe Miller
joe.miller@flotech.net

 

[JaneC]

You could have a column in your query as follows :

CountYes:iif([received],1,0)

this column will then hold 1 for yes and 0 for no and can be summed for the total

HTH

Jane