split results

[sandora]

V11,AccessDB This is an example of what my report looks like now.

GH1-Salesperson1-Mikey
GH2-TrailerType-LQ
Detail-Deal# Gross Commissions

GH1-Salesperson1-Howard
GH2-TrailerType-LQ
Detail-Deal# Gross Commissions

I have this report built and it works great, but now they've discovered that sometimes the Salespeople share deals and they need to be split between them. Each salesperson has a unique sales#. If only one person has the deal, the number is located in SP1 table, if there is a second person, theirs is in SP2. I can get the amouunt to split on the first person but it doesn't show up on the second. Also it doesn't show up on any deal that doesn't have a second person. The formula I'm using is:

if {sp1}={sp2} or {sp2}="" then
{retail} else
({retail}/2)

I'm thinking that might rather simplistic for what I want to do.

 

[TheBlondOne]

try something like the following

if isnull({sp2}) or {sp2} = "" or {sp1} = {sp2} then
{retial} else
({retail}/2)

Also you might want to check your linking between the tables and try a left outer join.

HTH

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[sandora]

Thanks, that formula worked great for getting them to show up where they weren't before except that the split deal is not showing up under the SP2's group of deals. Is that what the left outer join is for and can you elaborate?

 

[sandora]

My links look like this

Table1 Table2 Table3 Table4
Co#--------->Co#
Deal#------>Intro#
SP1---------->Emply#
Stock#-------------------->Stock#

All are Inner Joins except SP1 to Emply#, it's Left Outer. In table 2 is where SP2 also resides. If I do a LO join on SP2 to employee number almost all of my data goes away.