# Need help on summarizing data

#### pholt33

##### Board Regular
Hello, I am pretty new to using Access and cant figure out how to do something.

I have a table with sales data. Each record could potentially have two sales reps, each in their own field, who will split the total commission 50/50.

Rep 1.....Rep 2.....Sale \$
Bob........David.....10,000
David.......n/a......5,000
David......Mark......4,000

How do I combine the sales amounts when a rep could be in either column? For instance, David is in both columns and was part of 19,000 total sales, but 14,000 of that will be split with another rep, making Davids commissionable total \$12,000 (50% x 10k, 5000, and 50% x 4000).

Last edited:

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### james_lankford

##### Well-known Member
this might work, but I haven't tested it
Code:
``````select
rep,
sum (amt)
from
(
select
[Rep 1] as rep,
[Sale \$] as amt
from
your_table
where
[Rep 2] = 'n/a'

union all

select
[Rep 2] as rep,
[Sale \$] as amt
from
your_table
where
[Rep 1] = 'n/a'

union all

select
[Rep 1] as rep,
[Sale \$] * .50 as amt
from
your_table
where
[Rep 2] <> 'n/a'

union all

select
[Rep 2] as rep,
[Sale \$] * .50 as amt
from
your_table
where
[Rep 1] <> 'n/a'
) as tbl
group by
rep``````

#### Will_B

##### Board Regular
Speaking for myself, I'd use James' approach, but in case you want to stay within your current skill set, you can get the same result using two queries.

STEP 1: Create a new query, but add NO tables. View this query in "SQL View", which is one of the options along with "Design View" and "Datasheet View". Selecting "SQL View" will display a Notepad-like screen with the word "Select" shown.

STEP 2: Delete the word "Select" and paste this SQL string:

Code:
``````SELECT test.[Rep 1], IIf([Rep 2]="n/a",[Sale \$],[Sale \$]/2) AS SalesPersonShare
FROM test
UNION ALL SELECT test.[Rep 2], IIf([Rep 2]="n/a",0,[Sale \$]/2) AS SalesPersonShare
FROM test;``````

... then name this query "Salesperson sales share - 1 of 2" and close it.

STEP 3: Create another new query and go to "SQL View". Paste this SQL string:

Code:
``````SELECT [Salesperson sales share - 1 of 2].[Rep 1], Sum([Salesperson sales share - 1 of 2].SalesPersonShare) AS SumOfSalesPersonShare
FROM [Salesperson sales share - 1 of 2]
GROUP BY [Salesperson sales share - 1 of 2].[Rep 1];``````

... then name this query "Salesperson sales share - 2 of 2". This query will give you each person's share of sales revenue.

EXPLANATION
The first query uses what is called a UNION JOIN, which is the combination of two different queries. You cannot view this in "Design mode" because it cannot be represented visually. Within each part of the UNION JOIN is what is called an "Immediate If" statement. The syntax is: Some criteria; if criteria is true, do this; if criteria is false, do something else.

Replies
2
Views
302
Replies
10
Views
508
Replies
2
Views
260
Replies
7
Views
878
Replies
7
Views
600

1,191,532
Messages
5,987,123
Members
440,080
Latest member
drhorn4908

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back