detail section question

[Greg]

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

 

[Allen Browne]

The answer will depend on your data structure.

Presumably there is some table that has 2 records if there are 2
salespersons associated with a transaction. You can therefore get the number
of people with an expression such as this:
DCount("*", "TransactionTable", "TransactionID = " & [TransactionID])

So, you will divide the commission amount by that:
=[Commission] / Nz(DCount("*", "TransactionTable", "TransactionID = " &
[TransactionID]),1)

 

[Greg]

no, there is only 1 record per 'sale'. each record has a salesperson1 field
(which will always have a name in it) and a salesperson 2 field (which will
have a name about 10% of the time, the rest of the time will be null). what
i need is the detail section of the report for each salesperson to list each
record in which their name appears in the salesperson1 field (which it does
nicely right now) and also list EACH record that has their name in the
salesperson 2 field.

the closest i came is using an iif expression in the sorting/grouping menu
to state something like iif(["name of control for salepserson1"] =
[salesperson2], [salesperson2], [salesperson1]). in this case, it listed a
record for each item for salesperson1 as normal, and listed an item for each
record for salesperson2, but it was the wrong record for some reason. it was
just a duplicate of another record that had already printed.

i know i haven't explained this so it's easy to understand but i can't get
it out any more coherently w/o knowing what other information you need.
--
Greg D.

The answer will depend on your data structure.

Presumably there is some table that has 2 records if there are 2
salespersons associated with a transaction. You can therefore get the number
of people with an expression such as this:
DCount("*", "TransactionTable", "TransactionID = " & [TransactionID])

So, you will divide the commission amount by that:
=[Commission] / Nz(DCount("*", "TransactionTable", "TransactionID = " &
[TransactionID]),1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

Okay, the best solution is going to be to redesign the database so it is
normalized instead of these repeating fields.

A workaround would be to create a UNION query that combines both. This
example assumes the Amount field contains the amount of commission on the
transaction:

SELECT [salesperson1] AS Salesperson,
IIf([salesperson2] Is Null, [amount], [amount]/2) AS Commission
FROM Table1
WHERE [salesperson1] Is Not Null
UNION ALL
SELECT [salesperson2] AS Salesperson,
IIf([salesperson1] Is Null, [amount], [amount]/2) AS Commission
FROM Table1
WHERE [salesperson2] Is Not Null;

You cannot view a UNION query graphically. But you can make 2 queries that
give you what you need, switch them to SQL View (View menu), replace the
semicolon at the end of the first with the words UNION ALL, and then paste
in the SQL statement from the 2nd query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

no, there is only 1 record per 'sale'. each record has a salesperson1
field
(which will always have a name in it) and a salesperson 2 field (which
will
have a name about 10% of the time, the rest of the time will be null).
what
i need is the detail section of the report for each salesperson to list
each
record in which their name appears in the salesperson1 field (which it
does
nicely right now) and also list EACH record that has their name in the
salesperson 2 field.

the closest i came is using an iif expression in the sorting/grouping menu
to state something like iif(["name of control for salepserson1"] =
[salesperson2], [salesperson2], [salesperson1]). in this case, it listed
a
record for each item for salesperson1 as normal, and listed an item for
each
record for salesperson2, but it was the wrong record for some reason. it
was
just a duplicate of another record that had already printed.

i know i haven't explained this so it's easy to understand but i can't get
it out any more coherently w/o knowing what other information you need.
--
Greg D.

The answer will depend on your data structure.

Presumably there is some table that has 2 records if there are 2
salespersons associated with a transaction. You can therefore get the
number
of people with an expression such as this:
DCount("*", "TransactionTable", "TransactionID = " & [TransactionID])

So, you will divide the commission amount by that:
=[Commission] / Nz(DCount("*", "TransactionTable", "TransactionID = "
&
[TransactionID]),1)

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

 

[Greg]

your info on the union query was spot on. i didn't know anything about doing
it that way and it worked perfectly. thank you.
--
Greg D.

Okay, the best solution is going to be to redesign the database so it is
normalized instead of these repeating fields.

A workaround would be to create a UNION query that combines both. This
example assumes the Amount field contains the amount of commission on the
transaction:

SELECT [salesperson1] AS Salesperson,
IIf([salesperson2] Is Null, [amount], [amount]/2) AS Commission
FROM Table1
WHERE [salesperson1] Is Not Null
UNION ALL
SELECT [salesperson2] AS Salesperson,
IIf([salesperson1] Is Null, [amount], [amount]/2) AS Commission
FROM Table1
WHERE [salesperson2] Is Not Null;

You cannot view a UNION query graphically. But you can make 2 queries that
give you what you need, switch them to SQL View (View menu), replace the
semicolon at the end of the first with the words UNION ALL, and then paste
in the SQL statement from the 2nd query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

no, there is only 1 record per 'sale'. each record has a salesperson1
field
(which will always have a name in it) and a salesperson 2 field (which
will
have a name about 10% of the time, the rest of the time will be null).
what
i need is the detail section of the report for each salesperson to list
each
record in which their name appears in the salesperson1 field (which it
does
nicely right now) and also list EACH record that has their name in the
salesperson 2 field.

the closest i came is using an iif expression in the sorting/grouping menu
to state something like iif(["name of control for salepserson1"] =
[salesperson2], [salesperson2], [salesperson1]). in this case, it listed
a
record for each item for salesperson1 as normal, and listed an item for
each
record for salesperson2, but it was the wrong record for some reason. it
was
just a duplicate of another record that had already printed.

i know i haven't explained this so it's easy to understand but i can't get
it out any more coherently w/o knowing what other information you need.
--
Greg D.

The answer will depend on your data structure.

Presumably there is some table that has 2 records if there are 2
salespersons associated with a transaction. You can therefore get the
number
of people with an expression such as this:
DCount("*", "TransactionTable", "TransactionID = " & [TransactionID])

So, you will divide the commission amount by that:
=[Commission] / Nz(DCount("*", "TransactionTable", "TransactionID = "
&
[TransactionID]),1)

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