434,807 Members | 1,492 Online
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

# Every 5 orders a customer receives a discount.

 P: 2 I've made an ordering system which includes a query that counts how many orders each individual customer has made. Every 5 Orders a customer gets a discount on their order. How do I apply this? Is there a function in access that recounts values? For example, access would count all a customers orders. Once that customer reaches 6 orders the query would automatically change the value to 1 and start recounting again. Can this be done? Or do I have to do this via an expression whereby every nth term the discount applies? Any help is much appreciated! I've been searching this for hours! Dec 5 '11 #1
4 Replies

 Expert Mod 15k+ P: 31,489 Assuming the orders history is maintained in a table somewhere, a count of existing orders that match the particular customer would give you a value which would enable you to determine the price from some algorithm that is specified more clearly and precisely than you have done here. Dec 6 '11 #2

 P: 2 NeoPa, Perhaps I can elaborate on my situation so that maybe you can give me an answer that I would understand. I have 6 tables which are linked as so: Here are my queries: As you can see I have a query that's called "Number of Orders a Customer has made". This query includes a total count. Here is what that query displays: As you can see Stavro has made a total of 8 orders. In my system those who are members get a discount every 5th order they make. This discount is a discount of 50% off. So, when Stavro made his 5th order how do i make a query that would show that on this 5th Order Stavro received a discount of 50%. This query would then go into a Form whereby, whenever Stavro makes an order the count of his orders will keep going up until he reaches order number 10 which would then display that he gets a 50% discount so the discount will apply in this form to Stavro's total pay. I don't know how to apply this discount on every 5th order. Can this be done by query? If its not too much to ask can you explain to me step by step? If not, then i understand its cool. I appreciate your first reply =) Any extra help is much more appreciated! This is literally the last query I need to complete my databaseee. Once this is done the burden on my shoulders will be lifted! Dec 6 '11 #3

 Expert Mod 15k+ P: 31,489 Joe, Sorry for the long delay. I've looked at this a number of times in the intervening weeks but have never been able to make much sense of it. As I do from time to time I tried again tonight and I think I can see why I'm so confused by it. It's how you ask the question, as if the answer must be a query. It isn't at all of course, and I really should have just seen that when I first looked, but I just didn't. As far as I can tell, and though you've done a pretty good job here explaining and displaying information, your explanation doesn't include the bits that are important (for the obvious and quite understandable reason that it's never occurred to you what the important details are). Don't get me wrong. You did a good job. Actually though, certainly from my perspective and how I would approach this issue, the check would be done when every order is entered (almost certainly on a form somewhere). At the point of creating the new order, a check should be made to determine the number of previous orders (NB It's important to allow for no record which indicates none so far). It seems you already have a query designed that shows that info. This can be checked from within your order form using Recordset processing, or even more easily using a DLookup() (or probably a DCount() on the relevant table without even the need for the query). When you have the number - X - the result of (X Mod 5) should be equal to 4 if the next order needs to be discounted. If it isn't, then the price isn't discounted. Jan 23 '12 #4

 100+ P: 393 I'm not going to lie, but I barely read this post. However, the general gist is obvious and I have a piece of code I use all the time for various purposes and you can definitely use it here. Expand|Select|Wrap|Line Numbers Public Function MultipleOf(testVal As Long, multVal As Long) As Boolean      'test for 0 as either input     If multVal = 0 Or testVal = 0 Then         MultipleOf = False         Exit Function     End If       If (testVal - (Int(testVal / multVal) * multVal)) = 0 Then         MultipleOf = True     Else         MultipleOf = False     End IfEnd Function   The benefit is that you don't need to store anything extra, or reset any values anywhere. You just keep the sales records and send the count to the function. So if a customer has 187 total purchases, that's the number you send to the function along with the discount threshold of 5. So if MultipleOf(187,5)=True then they get it, otherwise they don't. In this case, they wouldn't. Like I said, I barely read the post and it looks like you've got a lot going on so you'll have to figure out how to work that in. Or just save it for later use. Jan 24 '12 #5