OK - do my best to explain properly.

I need to pull a list of 3 correlated ID #s from a table called 'sales_rep'

sales_region_number, sales_office_number, sales_rep_number (example data is 1500,1000,1000 - which is the full rep number when concatenated)

and when I pull that list (all sales_reps essentially) I need to get a COUNT of the number of sales (new orders) from 'btn_master' table. Which only has a primary key of customer_number, of which the sales_rep table doesn't have, it is located in customer_master. This is the query I use to get a list of sales_reps and the number of new orders... and it is verified to work...The date range is going to be a variable in an asp page but I can handle that... This list is a list of all reps and their respective offices and regions. But it only pulls the reps who have a new order in btn_master... I need a list of all reps and the count of new orders for those that have some, so some reps in the list would have a 0 as new order amounts in the count column....

Please dear god help me - I have only done sql for a few weeks and am at my wits end...

Below, the sales_rep table is related to the customer master table which is how I relate the rep number to btn_master and get sales amount... each new record (row) in btn_master is a new order. So I am just counting the system_btn field

The results so far can be viewed at http://lan.accxx.com/sqlquery.asp and choose the BTNS per sales office report from the drop down. But in order to compare sales to previous months - I need to include the reps with 0 new orders (or 0 rows in the date range from the btn_master table)... Table structure is right above the sample sql below...

HELP ME OBI ONE, YOUR MY ONLY HOPE....
Nick

TABLES^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^

sales_representative (NEED THIS WHOLE LIST)
            sales_representative.sales_region_number
            sales_representative.sales_office_number
            sales_representative.sales_rep_number

customer_master (THIS TABLE RELATED SALES REP NUMBERS TO BTN_MASTER)
            customer_master.customer_number
            customer_master.sales_region_number
            customer_master.sales_office_number
            customer_master.sales_rep_number

btn_master (EACH ROW MATCHING CUSTOMER_NUMBER IS NEW ORDER)
            btn_master.customer_number
            btn_master.system_btn




CODE BELOW^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^

SELECT sales_representative.sales_region_number,
    sales_representative.sales_office_number,
    sales_representative.sales_rep_number,
    COUNT(btn_master.system_btn) AS 'new_orders'

FROM sales_representative LEFT JOIN customer_master
    ON (sales_representative.sales_rep_number=customer_ma ster.sales_number_1) AND
        (sales_representative.sales_office_number=customer _master.sales_office) AND
            (sales_representative.sales_region_number=customer _master.sales_region_number)
    LEFT JOIN btn_master
    ON (customer_master.customer_number=btn_master.custom er_number)

WHERE (customer_master.effective_date BETWEEN '08/01/2003' AND '08/11/2003') AND
    (btn_master.system_btn <> 0)

GROUP BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number

ORDER BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number ;

UPDATE

I have gotten this far... altered code is below , but this now returns a count of the total and looks like this

sales_region | sales_office | sales_rep_number | New_orders
1500 1000 1000 470
1500 1000 1001 470
1500 1000 1002 470

and so on... the list of region, office, and rep numbers are correct (2,265 total) and the number of new orders are correct (470 total) but how do I get it to seperate so 470 is divided up among their respective sales reps... the table should result with this:

sales_region | sales_office | sales_rep_number | New_orders
1500 1000 1000 100
1500 1000 1001 200
1500 1000 1002 170

for example... the total orders are 470 but appropriately divided per sales rep...

UPDATED CODE^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

SELECT sales_representative.sales_region_number,
    sales_representative.sales_office_number,
    sales_representative.sales_rep_number,
    (
    SELECT COUNT(*)
    FROM customer_master RIGHT JOIN btn_master
        ON (customer_master.customer_number=btn_master.custom er_number)

    WHERE (customer_master.effective_date BETWEEN '08/01/2003' AND '08/11/2003') AND
            (btn_master.system_btn <> 0)

    )

FROM sales_representative

GROUP BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number
ORDER BY sales_representative.sales_region_number,
        sales_representative.sales_office_number,
            sales_representative.sales_rep_number ;

No need to post the same (lengthy) question in two different forums...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I was spazzing out under pressure - I will avoid duplicate posts from now on, thanks for the help.....

Hey there, I don't know if you found a solution for your problem, if not, and anyone else who suffers from similar derranged information retrieval work i've got a helpful suggestion. A service at www.Dictomail.com can make most all of these nightmares come to an end. It is formally an internet based messaging service (free online demo), however they offer alot of other features as well. There system has a survey function that would allow you to collect real-time results from all of your reps in the field with only one call. The menu system is voice or touchtone operated, and your incoming data can go to your e-mail, cellular phone, or palm device, or all of the above. Its your choice how to customize information delivery. All info is converted to text, including incoming voice-mail messages and its pretty cheap too. Check it out.

Try this...

SELECT sales_representative.sales_region_number,
    sales_representative.sales_office_number,
    sales_representative.sales_rep_number,
    (SELECT COUNT(btn_master.system_btn)
    FROM customer_master INNER JOIN btn_master
        ON customer_master.customer_number=btn_master.custome r_number
    WHERE (customer_master.effective_date BETWEEN '08/01/2003' AND '08/11/2003') AND (btn_master.system_btn <> 0)
                AND (sales_representative.sales_region_number=customer _master.sales_region_number)
            AND (sales_representative.sales_office_number=customer _master.sales_office_number)
        AND (sales_representative.sales_rep_number=customer_ma ster.sales_rep_number)
    ) AS 'new_orders'
FROM sales_representative
ORDER BY sales_representative.sales_region_number,
    sales_representative.sales_office_number,
    sales_representative.sales_rep_number ;



Cheers,
Pooja Falor