Total Column not returning correct amount

[Guest]

I have sales reps that have individual clients and clients that they share
revenue with ONE other rep. I've created a query to split this amount in
half. However, I need to have the split added to the individual reps total.

I.E.

Jackson $1,000
Smith $1,500
Jackson/Smith $4,000

Jackson's total should be $3,000...How do I tell access to perform this?
Thanks!

Brett

 

[Guest]

I had a similar problem and ended up setting a dynamic link to a Pivot Table
in Excel and doing the calculation there, could not find a way to do it in
Access even though I posted to the group a couple of time. However, maybe
someone will come up with a better solution for you.

 

[James A. Fortune]

I had a similar problem and ended up setting a dynamic link to a Pivot Table
in Excel and doing the calculation there, could not find a way to do it in
Access even though I posted to the group a couple of time. However, maybe
someone will come up with a better solution for you.


:

The following schema seems to be more flexible:

tblSales
SID AutoNumber PK
SaleAmount Currency

SID SaleAmount
1 $1,000.00
2 $1,500.00
3 $4,000.00

tblSalesReps
SRID AutoNumber PK
SalesRep Text

SRID SalesRep
1 Jackson
2 Smith

tblSalesRepSales
SRSID AutoNumber PK
SID Long FK
SRID Long FK
Percentage Double

SRSID SID SRID Percentage
1 1 1 100
2 2 2 100
3 3 1 50
4 4 2 50

Post back if you need help with the SQL required to get the totals for
the Sales Reps.

James A. Fortune
(e-mail address removed)

Access Tip:

When creating new fields on a report by dragging fields from the Field
List, change the default control name to a name that's different than
the field name. That way, when summing a field, Access won't try to sum
the contents of the control, which may contain formatting, and will
always use the value from the query.