Counting Commissions in a Report

  • Thread starter Thread starter Jeff Garrison
  • Start date Start date
J

Jeff Garrison

Hello all. I have a database that tracks Commissions for recruiters (Access
2003/2003). I need to come up with a commission report on a monthly basis,
which is not a problem. Where I'm having the logic problem is....

Sub-Contractor XYZ works on Project 123.

He works at site ABC from 10/1/07 - 10/1/07.
He works at site DEF from 10/3/07 - 10/3/07.
He works at site GHI from 10/6/07 - 10/6/07.
He works at site JKL from 10/9/07 - 10/9/07.

etc...

I need to generate a report that shows for the first site, the commission
would be $5.00. The second site and onward would be $2.50. I would like to
total on the Project and on the Sub-Contractor. I don't want to store the
data in the table, since that's not good practice....the calculation need to
take place at the report level.

I've seen some posts saying to use the DCount function, but I'm at a loss of
where to go from here.

Any help would greatly be appreciated.

Thanks.

Jeff
 
Assuming that you've got a table that has four rows for the sites and dates
at which each sub-contractor worked, you'd use DCount like:

DCount("*", "[MyTable]", "[SubContractor] = 'XYZ' " & _
"AND [ActiveMonth] BETWEEN #10/01/2007# AND #10/31/2007#)
 
I have a table that contains about 7000 records (tblContractorProjects),
with dates going back a year or so. I need to put a field in my record
source that returns the value of the commission to be paid. The fields in
the table are as follows...(there are others, but these are the relevant
ones)

contractContractor (contractor's unique number)
contractProjectNumber (the project they worked on)
contractStartDate (the date they started the project)
contractEndDate (the date they completed the project)

The reports record source currently has this table, a project information
table, and a employee table and the user is prompted for the date range of
the contractStartDate.

What I need to is to have a field in the SQL record source that comes back
with $5.00 if it is the first project of the month (or date range) and $2.50
if it is the second, third, etc., meaning if they work on the same project
more than once in a month, the first one is $5.00 and the rest are $2.50.


Douglas J. Steele said:
Assuming that you've got a table that has four rows for the sites and
dates at which each sub-contractor worked, you'd use DCount like:

DCount("*", "[MyTable]", "[SubContractor] = 'XYZ' " & _
"AND [ActiveMonth] BETWEEN #10/01/2007# AND #10/31/2007#)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Garrison said:
Hello all. I have a database that tracks Commissions for recruiters
(Access 2003/2003). I need to come up with a commission report on a
monthly basis, which is not a problem. Where I'm having the logic problem
is....

Sub-Contractor XYZ works on Project 123.

He works at site ABC from 10/1/07 - 10/1/07.
He works at site DEF from 10/3/07 - 10/3/07.
He works at site GHI from 10/6/07 - 10/6/07.
He works at site JKL from 10/9/07 - 10/9/07.

etc...

I need to generate a report that shows for the first site, the commission
would be $5.00. The second site and onward would be $2.50. I would like
to total on the Project and on the Sub-Contractor. I don't want to store
the data in the table, since that's not good practice....the calculation
need to take place at the report level.

I've seen some posts saying to use the DCount function, but I'm at a loss
of where to go from here.

Any help would greatly be appreciated.

Thanks.

Jeff
Click to expand...
Click to expand...
 
Hello Jeff,

You may want to use a query similar to the following to use a left join a
intermediate table that get the first project for a contractor:


select t1.contractProjectNumber , t1.contractContractor ,
t1.contractStartDate ,

iif( t2.date3 =t1.contractStartDate ,5, 2.5)

as fee
from work t1 left join


(select min(contractStartDate) as date3, project, contractContractor from
work group by contractProjectNumber, contractContractor ) as t2


on (t1.contractStartDate =t2.date3 and t1.contractProjectNumber
=t2.contractProjectNumber and t1.cu=t2.contractContractor )


If you have further questions or concerns, please feel free to let's know.
Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top