Need Help with query and 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.

 

[jjonesal]

I'm not sure I fully understand the question, but... I think you should be able to add a calculated field to your query recy:IIF(received = yes,1,0). This will provide a field for each record (1 if it is received and 0 if it isn't). Then, you can sum field for each group in the query or on the report. Of course, to sum in the report, you'll need to put the field in the detail section of the report. But since you don't want it to display, just set the visible property to No.

You could also create recn:IIF(received = yes,0,1) for the no's.

Hope I understood your dilemma and hope this helps.





J. Jones
jjones@cybrtyme.com