Counting Commissions in a Report

[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

 

[Douglas J. Steele]

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#)

 

[Jeff Garrison]

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.

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!)

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