Query on getting summaries for unique names 1

[mmiram]

Hi all:

Here is the problem I have. The table I have has the following fields:

Address
Price
City
State
Zip
FirstName
LastName
SalesType

"Sales Type" can be 1 or 2. First Name and Last Name correspond to the Sales Person's name. I need to be able to build a query that will count the number of addresses each sales person has sold and the sum of his sales. The query would be logically as follows

Select DISTINCT FirstName&" "&LastName, sum(Price), Count(Price)
group by DISTINCT FirstName&" "&LastName;

However, it does not work because there are transactions where the sales person could have "sales type" 1 or 2 for the same address. So an address would be entered twice for the same sales person (once for SalesType 1 and once for 2) and the above query would count that as two transactions. At the same type I cannot add "Having SalesType = 1 or 2" becuase there are some sales people who have either 1 or 2 as the sales type but not both.

I hope the explanation is clear. What should I use to solve this issue?

Ideally I want the sum and count for each address.

Thanks.

 

[Melagan]

I'm pretty sure I know how to solve this. Give me some time to test and I will post the SQL, unless PHV beats me to it =)

 

[mmiram]

I just thought I would add some more info which might make the question clearer. Each address definitely is entered as two rows once for "SalesType" 1 and once for "SalesType" 2.

For each salesperson, the address should be counted only once (even if the sales person has both the salesTypes).

However, if two different sales people each are part of the transaction (i.e. Bob has salesType 1 and Mark has salesType 2) then the address can be counted once each for each sales person. But if Bob has both salestype 1 and salestype 2 for the same address, it should be counted only once for him.

P.S. The last sentence of my previous post should have read "Ideally, I want the sum and count for each sales person". Sorry about the typo.

 

[r937]

there may still be a problem

could you please show some sample rows of actual data, some where it's the same person for both type 1 and type 2, some where it's different, some where there's only a type 1, some where there's only a type 2

r937.com | rudy.ca

 

[mmiram]

Hi:

Here is a link to an image of the sample data

http://www.geocities.com/dsrm1979/sample.htm

The query should give the following results based on the data shown:

Name Count Sum

Rob Daniels 2 $900,000
Tony Bennett 1 $200,000
Daisy Lee 2 $950,000
Anthony Mason 1 $50,000

Thanks

 

[r937]

okay, try this --

select FirstName&" "&LastName as FullName
     , count(Price) as CountOfPrice
     , sum(Price)   as SumOfPrice
  from (
       select distinct
              Address
            , Price
            , City
            , State
            , Zip
            , FirstName
            , LastName
         from transactions
       ) as youneek
group
    by FirstName
     , LastName

r937.com | rudy.ca

 

[Melagan]

I was able to produce the desired results using the following SQL, but given your example table referenced on Geocities, shouldn't Rob Daniel's income be:

Rob Daniels 3 $1,050,000 ?

At any rate, here it is:

SELECT [FirstName] & " " & [LastName] AS FullName, Count(t.FirstName) AS rCount, Sum(t.Price) AS rSum
FROM tblYourTable AS t
GROUP BY [FirstName] & " " & [LastName], t.LastName
ORDER BY t.LastName;

 

[Melagan]

Sorry man, I just wasn't paying enough attention to your needs...this query gives you what you don't want. I'm taking another stab at it.

 

[Melagan]

Nevermind altogether, r937's SQL works just fine; wasn't paying attention again =P

 

[mmiram]

Hey R937 and Melagan:

I tried using the query by R937 and I am getting errors. The first time I tried it, I got the following error:

"The Microsoft Jet Database Engine cannot find the table or query (Select distinct Address, Price, City, State, Zip, FirstName, LastName from DC) Make sure it exists and that its name is spelled correctly."

I typed in the exact query that R937 posted. DC is the table name and it exists. All my other queries are written on DC.

Any idea why this would happen.

 

[mmiram]

oops...sorry...I messed up with the query. In the Select clause in the sub-query I did not type in "price".

Once I added price in, it works fine. Thanks a lot for your help. You guys really helped me out here.