• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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
0
kwilsokl
Asked:
kwilsokl
  • 5
  • 5
  • 2
  • +1
4 Solutions
 
ddanonimityCommented:
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>')
0
 
GRayLCommented:
Want to try that question again?  I see four different months in the example where you need six of the same month??
0
 
kwilsoklAuthor 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
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
OdeMonkeyCommented:
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.
0
 
kwilsoklAuthor 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


0
 
OdeMonkeyCommented:
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.
0
 
GRayLCommented:
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.
0
 
kwilsoklAuthor 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
0
 
GRayLCommented:
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.
0
 
kwilsoklAuthor 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
0
 
GRayLCommented:
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))
0
 
kwilsoklAuthor Commented:
Thanks GRayL:

That worked, I have  alot to learn but thsi really helped
0
 
GRayLCommented:
Thanks, glad to help.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now