Help with sql query

[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.

 

[Douglas J. Steele]

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

 

[daicamad]

Thank you! :^)