detail section in a report

[kaosyeti]

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

[Bob Quintal]

"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote
in 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.

[kaosyeti]

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:

hey all. i have a report for sales commissions that i'm
having a little trouble with. each transaction may have

[quoted text clipped - 19 lines]

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;

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

[Bob Quintal]

"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote
in news:59fa7fb23cec9@uwe:

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.

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.

Bob Quintal wrote:

hey all. i have a report for sales commissions that i'm
having a little trouble with. each transaction may have

[quoted text clipped - 19 lines]

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.

[kaosyeti]

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:

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.

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.

hey all. i have a report for sales commissions that i'm
having a little trouble with. each transaction may have[quoted text clipped - 23 lines]salestable
ORDER BY salesperson;

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