detail section in a report

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200512/1

re: detail section in a report

"kaosyeti@comcast.net via AccessMonster.com" <u15580@uwe> wrote
in news:59abd121a26e4@uwe:
[color=blue]
> 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
>[/color]
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.

re: detail section in a report

your info on the union query was spot on. i didn't know you could use a
query this way. it worked perfectly. thank you.

Bob Quintal wrote:[color=blue][color=green]
>> hey all. i have a report for sales commissions that i'm
>> having a little trouble with. each transaction may have[/color]
>[quoted text clipped - 19 lines][color=green]
>> to access so let me know if i left out any needed info to
>> figure this out. thanks[/color]
>
>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;
>[/color]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200601/1

re: detail section in a report

"kaosyeti@comcast.net via AccessMonster.com" <u15580@uwe> wrote
in news:59fa7fb23cec9@uwe:
[color=blue]
> your info on the union query was spot on. i didn't know you
> could use a query this way. it worked perfectly. thank you.
>[/color]

Glad to have helped.

I still recommend you move the salespersons info to a child
table, for the reasons mentioned below. Access makes it so easy.

[color=blue]
> Bob Quintal wrote:[color=green][color=darkred]
>>> hey all. i have a report for sales commissions that i'm
>>> having a little trouble with. each transaction may have[/color]
>>[quoted text clipped - 19 lines][color=darkred]
>>> to access so let me know if i left out any needed info to
>>> figure this out. thanks[/color]
>>
>>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;
>>[/color]
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

re: detail section in a report

to be quite honest, i have no idea what a child's table is. i only touched
access for the first time in sept 05 and before that had done minor work with
excel. (like a 5 step nested if statement in excel - Not VBA - would be my
most complicated formula before about a year ago when i stepped it up). as i
got my learn on with excel, i had a new project that excel just couldn't
handle. that's when i discovered access. i've written a pretty decent db so
far but i have never touched access before 5 months ago and have never, EVER
even heard of vba before then. i get most of my info from sites like these,
then i adapt and add it to my db. once i do that, i can usually morph it on
my own into other areas of the db but not every time for sure.

i guess i'm telling you this because if you'd like to take the time to
further explain your suggestion which completely evaded me the first time,
i'd love to hear it. if you can't dumb-it-down enough or think it'll be too
long to explain here, that's fine too. i just got the hint that you were
genuinely concerned about the results my db will experience in the future so
i felt obligated to find out what you were driving at (especially now that
i've given it to 3 of our other stores to use for tracking their customers).

thanks for your time and help -- it was a huge leap forward for me to get to
know queries a little better. my first report was based on a simple query
based on a simple table, but since then my 8 or so other reports have all
been right off of the tables, a practice i am going to discontinue as of
right now, let me assure you.


Bob Quintal wrote:[color=blue][color=green]
>> your info on the union query was spot on. i didn't know you
>> could use a query this way. it worked perfectly. thank you.[/color]
>
>Glad to have helped.
>
>I still recommend you move the salespersons info to a child
>table, for the reasons mentioned below. Access makes it so easy.
>[color=green][color=darkred]
>>>> hey all. i have a report for sales commissions that i'm
>>>> having a little trouble with. each transaction may have[/color][/color]
>[quoted text clipped - 23 lines][color=green][color=darkred]
>>>salestable
>>>ORDER BY salesperson;[/color][/color]
>[/color]

--
Message posted via http://www.accessmonster.com