Dsum issue when more than one column is used

[jerseyaccess]

I am using Access 2013 and have a report which is working fine.



It sums the number of times each sales person's name appears in a column and adapts when I use a filter i.e. if I limit records to the current month only, the total adapts accordingly. There is a different total field for each salesperson.

I have two relevant columns. The first is 'LeadNegotiator' and the second is 'NegotiatorTwo. Both sum as described in the previous paragraph.

Here's the problem... There are times when the salesperson shares a sale with another salesperson. So instead of the whole sale being counted as one (to one person) it is counted as half a sale each.

Formulas are as follows...

=Sum(IIf([LeadNegotiator]=7,1,0)) with the '7' changing depending on the salesperson

=Sum(IIf([NegotiatorTwo]=7,1,0)) with the '7' changing depending on the salesperson

How do I adapt my code to reflect this?

Any help greatly appreciated

[CJ_London]

it feels like your data is not normalised so makes this type of calculation difficult. Also, what happens if you have a third negotiator?

You should have a table which lists one negotiator and their role in one record - something like

tblNegotiators
NegotiatorPK autonumber
SaleFK long - links to sales detail table
EmployeePK - long - links to employee table
Role - could be text, could be a link to another table, could be just a number to indicate level (1=Lead negotiator, 2=negotiator2, 3 =negotiator3 etc) or may not be required at all if shares are always evenly split

then your query becomes very straight forward

otherwise I would guess that something like this might do the trick

=Sum(IIf([LeadNegotiator]=7 or [NegotiatorTwo]=7,1,0))

[orange]

I agree with Ajax that you probably have a table design/normalization issue.
I recommend you show us a copy of your tables and relationships (jpg), and a clear description of the business as it concerns Sales and the people involved.

With well designed table(s) it sounds like an query with a Count and Group By might be appropriate. But your shared Sale might not fit.

Good luck.