Need help on summarizing data

[pholt33]

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

 

[james_lankford]

this might work, but I haven't tested it

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]

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:

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:

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.