DB Linking Problem, maybe?? 1

[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 field SP1 of table2, if there is a second person, theirs is in field SP2 of table2. I've used this formula:

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

and it returns all of retail values and splits the deal on SP1 but it doesn't show up in the detail on SP2 (the other half of the deal).

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. Any suggestions?

 

[hilfy]

What other tables are included besides SP1 and SP2? I assume a link to an Employee table on Emply# to get the name.

In order to get the employee name based on SP2 in addition to SP1, you'll have to include a second copy of the Employee table with a different alias.

I've got some other thoughts about how you're grouping, but I'll need to know more about how your data's set up.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[sandora]

Okay, I added a second Emply# table alias. It is linked the same way as the other table. Still not showing up on the second person. Not what info you are looking for but here goes:

DB1: Emply Table, Emply Table1
DB2: Inventory table
DB3: Intro table, Profit table

Links:
Profit;Deal# to Intro;Deal#
Intro;Stock# to Inventory;Stock#
Intro;SP1 to Emply;Emp#
Inrto;SP2 to Emply1;Emp#
Emply;Co# to Inventory;Co#
Emply1;Co# to Inventory;Co#

If you need more info, let me know.

 

[sandora]

I think the problem may be with the @salesperson formula:

{Emplytable.Lastname}&", "&{Emplytable.Firstname}

I think I need for it to look at the alias table also but how do I do that? Or am I completly off base?

 

[hilfy]

Do you have to group this on salesperson or can you group on Deal and show both? If you have to group on salesperson, you may actually need to do a subreport to get the Deal information where the salesperson is the SP2 field. The problem with that is that you won't be able to group on Deal for the salesperson because you'll get all of the SP1 deals and then all of the SP2 deals (or vice versa...)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.