Dividing and summing a field

[Haji]

Hello,

I have a table with three fields. Practitioner1,
Pracitioner2 and Revenue. My data looks like this:

Pract1 Pract2 Sales
Joe Joe Smith 1000
Jim Jim Jones 500
Steve Steve Williams 500
Combined1 Joe Smith 200
Combined2 Jim Jones 300

I want to sum the sales by practitioner but I only want
to give joe and jim half the credit for combined sales.
So joe would get an extra 100 rather than 200 and jim
would get an extra 150 rather than 300. I then want to
take these halved values and sum them with the rest. The
query should look like this:

Pract1 Pract2 Sales
Joe Joe Smith 1100
Jim Jim Jones 650
Steve Steve Williams 500

Can anyone help me with this?

Thanks,

Haji

 

[Dale Fye]

Haji,

Try the following. What this does is create a computed field for the sales
based on whether the value in the Pract1 column contains the word
"combined". If it does, it divides the sales in half, otherwise, it
provides 100% of Sales.

SELECT tbl_Practioner.Pract2,
Sum(IIf(InStr([Pract1],"combined")>0,[sales]/2,[Sales])) AS
Expr1
FROM tbl_Practioner
GROUP BY tbl_Practioner.Pract2;

HTH
Dale