# Help to calculate totals based on item count

ok here is what I am trying to do in an access report. I have no idea on where to begin.

I have a report that lists dates for each customer who was contacted by a Sales person .  There are like hundreds of customers.
I am trying to create an invoice out of the report that we can send out to the Sales letting them know what they earned in a month by how many contacts they have

If a Sales person has at least 6 contact dates for one customer a month they get \$200  but it has to be minimum of 6 contact dates for new customers.

If they a customer already they get \$75 per contact date but it has to be minimum of 2 contact dates for new customers and no more then 2.

So 6 contacts = \$200 for a new customer
2 = \$75 for a current customer

I am guessing I need to do a count on the contact dates so if 6 = 200 else if 2 = 75
So the report is something like this

New Customer    Contact Date  (paid for 6 contacts at \$200 with a minimum of 6 contacts)
Jon Doe         11/01/08
11/07/08
12/12/08
12/12/08
10/12/08
09/12/08

Current Customer    Contact Date  (paid for 2 contacts at \$275 with a minimum of 2 contacts)
Jon Doe         11/01/08
11/07/08
11/07/08
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Not sure how it works with access.but with SQL I would do something like this:-

Select COUNT(*) from <table name> where(<client_date> = '<date>')
Commented:
Want to try that question again?  I see four different months in the example where you need six of the same month??
Author Commented:
Yes Sorry I just grabbed some random dates but yes you are correct the dates will be within the same month as the report will be ran monthly

to make the question easier?? I was just told by the department head  that if the contact dates equal 6 then it is \$200 if less than 6 then it is \$75. I also forgot to mention that I have a unbounded text box that counts the total number of dates so if there were 7 contact dates the total box says 7. I just need to add some code to say 7 = \$200 since we are only counting 6 total. and if they had 5 then it would be \$75 ince it is less then 6

Sorry their process is so messed up. some people will call a customer 9 times in a month but they only get paid for a total of 6
Commented:
Create a module and add this function:

function GetCommission(inCnt as integer) as currency
Case select inCnt
Case is > 5
GetCommission=200
Case is > 0
Get Commission=75
Case Else
GetCommission=0
end select
end

In your summation query that is grouped by ContactName and limited to a particular month, add a column, Commission: GetCommission(CountOfContactDate).

I like this approach because the logic can be very complex and still be clear in its intent when it is in a function, and if the rules change, you can simply change the function to match with no need to modify the query.
Author Commented:
Hi OdeMonkey:

I dont have a sum query I just have a text box on the report footer that is =Count([ContactDate])

so it will display total - 6 in the unbound text box if they had six contacts

Commented:
Then you can call the function, GetCommission, to calculate the value of another unbound text box in the same footer using the value in the total text box.
Commented:
In a new text box tbxCommission enter this as the control source in the footer

IIf Count(ContactDate)>=6, 200, IIf(Count(ContactDate)>=2, 75,0))

If the count is greater than or equal to 6, \$200, if between 2 and 5, \$75, else 0 - that what you want?  BTW format the box as Currency.
Author Commented:
GRayL:

that is exactly what I am looking for

but when I run the report it pops up as a parameter and is requiring me to input data
Commented:
If you have a space in the field name (God forbid!) then:

IIf Count([Contact Date])>=6, 200, IIf(Count([Contact Date])>=2, 75,0))

Any time you get a parameter when you don't expect it, look at the spelling of your tables and fields.  Lest you miss my point, try to avoid using spaces, underscores and special characters in table, field and variable names - otherwise, you have to wrap them in brackets [].  ContactDate is a lot easier to type that [Contact Date] and is just as readable.
Author Commented:
GRayL:

Sorry to be such a pain. I am a network engineer that fell into this database programming. and I am still learning. I checked the spelling and took some screen shots of what I am seeing. I hope it helps. I would also like to thank everyone who has helped. I know there are a lot of people who just post to get the problem solved for themselves and don't want to do the research or work. But I am trying to understand and learn so I can help others that are new when the time comes.

I tried different control sources so that is why you see a different field name in the screenshot I attached. Access keeps telling me the control source is invalid. So I tried different fields to do the count on.
Document.pdf
Commented:
If the control source of a control is not purely a field name, it must be preceded with an equal sign:

=IIf Count([Contact Date])>=6, 200, IIf(Count([Contact Date])>=2, 75,0))
Author Commented:
Thanks GRayL:

That worked, I have  alot to learn but thsi really helped

Experts Exchange Solution brought to you by