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 want to do the calculation on the fly, since it's not good practice to store calculated values in tables.

I've seen some posts elsewhere saying to use the DCount function, but I can't seem to get it right.

Any help would be appreciated.

Thanks.

Jeff G

I want to do the calculation on the fly, since it's not good practice to store calculated values in tables.Looks like someone has been researching the forum. :)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.Jeff,

If you want my advice, I think there is too little information here to provide a good answer, or even one that is closely related to the solution. Could you maybe post your table structure, or some screenshots or something? That would help, unless you're just looking for some general comments and ideas on the issue you're having. A few things that might help you get started...

**DCount is not used for calculations, it counts occurances of a value in a field.
**Depending on the table setup, it sounds like you want to calculate a number by a grouping. This FAQ (http://www.access-programmers.co.uk/forums/showthread.php?t=135763)might give some relevant information on that.
**A conditional statement or two with the report would probably be good. Look at the SELECT CASE and IIF statements. They might be good candidates for your needs.

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.

See if that makes sense. If not, I can try to elaborate more....

The $5 and $2.50 conditional statement would be easy to write. You would need it combined with a DLookup, depending on the other method you used for the dates.

The only question I would have for something like this is:
*Do the commission amounts apply based on the START DATE of the project work period, or based on an entire work period that is contained within a month's date ranges? Is there any effect on the commission amount of any given work period if the range of that period spans two different months?

That might be a hang up, but if an overlap like that doesn't matter, you probably wouldn't have much trouble with this...

The commission would be based on the Start Date of the Project. One of the report's criteria is based the Start Date being between 2 dates that is prompted for the user to input. The report would only look at the Start Date that is between the 2 inputted dates.

Well, if the report has criteria before it is opened, it must be based on a query then, right? This might be a bit more difficult than I thought. Your statements are starting to conflict, Jeff. Specifically, I mean these two...The commission would be based on the Start Date of the Project. One of the report's criteria is based the Start Date being between 2 dates that is prompted for the user to input.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.Are you wanting to put into effect, this process of yours, but using it on a filtered recordset? That's what it sounds like; filtered on the user-entered parameters. That's just a recordset in its own right.

Do you have a plan thus far? Have you used any of the information to figure out how to do this? I'm just wondering. It would help a bit, at least to add to the ideas I would have here too...

thinking out loud here.
= could you not use the count fuction have the items listed in a report and have if count = 1 then value = 5 and if >1 value = 2.5
so
1 ABC then Value $5.00
2 XYZ then vlaue $2.50
3 ZZZ then Value $2.50

so i am looking at the count and using the numeric value to then assing a value to a textbox on the report ..
might not be do-able - but its an idea