Help with sql query

  • Thread starter Thread starter daicamad
  • Start date Start date
D

daicamad

Hi everyone,

I have a problem with sql query statement. Since I work on Access, I
hope I come to the right place.

I use this query:
SELECT (UserInfo.FirstName),(UserInfo.LastName), (SoldItems.PriceSold
- SoldItems.Discount) as Sale
FROM(UserInfo INNER JOIN SoldItems ON UserInfo.EmpID =
SoldItems.EmpID) INNER JOIN [Transaction] ON SoldItems.TransID =
Transaction.TransID
WHERE DateValue([Transaction].[Time]) = #4/24/2007#;

And got this:
FirstName LastName Sale
Tony Smith $3.00
Tony Smith $13.00
Tony Smith $22.00
Tony Smith $3.00
Tony Smith $3.00
Lisa McKenzie $38.00

Now I want to make sum of sale for each person, so that I can have
this as final:
FirstName LastName Sale
Tony Smith $44.00
Lisa McKenzie $38.00

How I do that above?

I tried Sum function for Sale field, but it complains about FirstName
and LastName.
 
SELECT UserInfo.FirstName, UserInfo.LastName,
Sum(Nz(SoldItems.PriceSold, 0) - Nz(SoldItems.Discount,0)) as Sale
FROM (UserInfo INNER JOIN SoldItems ON UserInfo.EmpID =
SoldItems.EmpID) INNER JOIN [Transaction] ON SoldItems.TransID =
Transaction.TransID
WHERE DateValue([Transaction].[Time]) = #4/24/2007#
GROUP BY UserInfo.FirstName, UserInfo.LastName
 
Back
Top