detail section in a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kaosyeti@comcast.net - 31 Dec 2005 19:46 GMT

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

Reply to this Message

Bob Quintal - 31 Dec 2005 20:12 GMT

> 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;

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message

kaosyeti@comcast.net - 07 Jan 2006 01:57 GMT

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.

>> 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;

Reply to this Message

Bob Quintal - 07 Jan 2006 03:39 GMT

> 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;

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message

kaosyeti@comcast.net - 10 Jan 2006 17:45 GMT

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.

>> 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.
[quoted text clipped - 9 lines]
>>>salestable
>>>ORDER BY salesperson;

Reply to this Message