Re: detail section in a report
- From: Bob Quintal <rquintal@xxxxxxxxxxxx>
- Date: Sat, 31 Dec 2005 20:12:48 GMT
"kaosyeti@xxxxxxxxxxx via AccessMonster.com" <u15580@uwe> wrote
in 59abd121a26e4@uwe:">news:59abd121a26e4@uwe:
> hey all. i have a report for sales commissions that i'm
> having a little trouble with. each transaction may have
> either 1 salesperson (90% of the time) or 2 salespeople. in
> the event that there's 2, each of them get half credit for the
> sale and half of the commissions earned. my problem is that
> my report (rptcommissions) doesn't show the detail of each
> record if the named salesperson is 'second' in this particular
> transaction.
>
> to clarify, if john smith has a sale split with jane doe, each
> is considered to be half a sale but john is the primary
> salesperson (field salesperson1). in my report, it will list
> all of the records for john on page 1, including this split
> sale. the problem is that for jane, it will list all of the
> records where she was salesperson1 on her page, but i need it
> to also list (seemlessly, if possible) the records where she
> is salesperson 2 and it doesn't do that.
>
> i understand that it's because my report is grouped by the
> salesperson1 field, but i don't know how i can show the detail
> for any back-half split sales. i'm still green when it comes
> to access so let me know if i left out any needed info to
> figure this out. thanks
>
To do this right, you are going to redesign the structure so
that you move the salespersons in a child table, with the fields
for the transaction ID, (as the foreign key back to the
transactions table), and the split fraction (0.5) if split
equally between 2 people, 1 if alone, It will also allow you to
split up a sale 3 or more ways, when that situation will
eventually come up.
You then base your report on a query that joins the salespersons
to the sales.
If you want to keep your existing single table structure, you
could try writing the SQL for a union query to bring in the
salesperson2 field to the salesperson1 position
e.g.
SELECT saleID, saledate, saleperson1 as saleperson from
salestable
UNION SELECT saleID, saledate, saleperson2 as saleperson from
salestable
ORDER BY salesperson;
--
Bob Quintal
PA is y I've altered my email address.
.
- References:
- detail section in a report
- From: kaosyeti@xxxxxxxxxxx via AccessMonster.com
- detail section in a report
- Prev by Date: Re: DAO peculiarity in A97?
- Next by Date: Re: =Iff(([Me]![Gender])="M","Male","Female")
- Previous by thread: detail section in a report
- Next by thread: Change event trigger
- Index(es):
Relevant Pages
|
