SQL Query Question.

X-No-Archive:yes

I thought I'd see if you guys can help me with an SQL query that even
Oracle admins couldn't solve without using an Oracle only function
DECODE. I don't have DECODE on the DBMS I'm using, so I thought maybe
the PhD SQL theorists may be able to help me. Thanks in advance!

I have a Query Called "Lost Sales" which looks like this:

SELECT leads.salesman AS Salesperson, COUNT(lost) AS [Number of Lost]
FROM leads INNER JOIN salesman ON leads.salesman = salesman.salesman
WHERE lost<>0
GROUP BY leads.salesman;

I then have a query called "Sold Sales" which looks like this:

SELECT leads.salesman AS Salesperson, SUM(amount) AS Totaled,
COUNT(sold) AS [Number of Sales] FROM leads INNER JOIN salesman ON
leads.salesman = salesman.salesman
WHERE sold <> 0
GROUP BY leads.salesman;

I then have a query called "Total" which combines the two previous
queries and looks like this:

SELECT * FROM [Lost Sales] RIGHT JOIN [Sold Sales] ON [Lost
Sales].[Salesperson] = [ Sold Sales.Salesperson];

Now my problems. The leads table looks like this
salesperson
unique_id (primary key)
amount
sold (yes/no)
lost (yes/no)
pending (yes/no)

* yes = -1 and no = 0

When I run the "Total" query I get two salesperson columns in the
result where I'd prefer to get only one. And sometimes when the person

doesn't have a sold sale or lost sale checked in the leads table their
name is not there. Here is an example to help you visualize it:

Lost Sales.Salesperson|Number of Lost|Sold
Sales.Salesperson|Totaled|Number of Sales
-----------------------------------------------------------------
|Jim | 3 | Jim | $1,000 | 3 |
-----------------------------------------------------------------
| | | Greg | $2,000 | 4 |

What I would like the result to be is this though:

Salesperson | Totaled | Number of Lost | Number of Sales
---------------------------------------------------------------------------
------------
Jim | $1,000 | 3 | 3
---------------------------------------------------------------------------
------------
Greg | $2,000 | 0 | 4

.

Relevant Pages

  • Re: please help a newbie solving a problem
    ... my form is a form based on a query ... salesperson, but the appropriate data will not change meaning if I ... a sales person with 8 different type of activities, it will display ...
    (microsoft.public.access.gettingstarted)
  • Challenging SQL Query Problem. Can you solve it?
    ... I have a Query Called "Lost Sales" which looks like this: ... SELECT leads.salesman AS Salesperson, ... I then have a query called "Sold Sales" which looks like this: ... COUNTAS FROM leads INNER JOIN salesman ON ...
    (comp.databases.oracle.misc)
  • Re: please help a newbie solving a problem
    ... a macro is pretty easy. ... not sure if your screen is form or query or report or what - - but in any ... a sales person with 8 different type of activities, ... the form as an unbound combobox based on the salesperson name from the ...
    (microsoft.public.access.gettingstarted)
  • Re: please help a newbie solving a problem
    ... a sales person with 8 different type of activities, it will display all 8 ... I crated a form based on a query. ... the form as an unbound combobox based on the salesperson name from the ...
    (microsoft.public.access.gettingstarted)
  • Re: cant find the how-to steps for query results I need
    ... What are the field types of the Step fields and the Sold field? ... You can build this query in design view. ... I have a table that basically reads like this...date, salesperson, step 1, ... I need to create a query that spits out the numbers for each sales person on a month to date basis. ...
    (microsoft.public.access.gettingstarted)