473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Every 5 orders a customer receives a discount.

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 1976
NeoPa
32,556 Expert Mod 16PB
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
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
NeoPa
32,556 Expert Mod 16PB
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
dsatino
393 256MB
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
  1. Public Function MultipleOf(testVal As Long, multVal As Long) As Boolean
  2.      'test for 0 as either input
  3.     If multVal = 0 Or testVal = 0 Then
  4.         MultipleOf = False
  5.         Exit Function
  6.     End If
  7.  
  8.     If (testVal - (Int(testVal / multVal) * multVal)) = 0 Then
  9.         MultipleOf = True
  10.     Else
  11.         MultipleOf = False
  12.     End IfEnd Function
  13.  
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

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: SusieS | last post by:
I'm trying to set up a simple relational Access database to look after customer orders for our small bookselling operation. I'm a beginner at this, teaching myself via Help files and the manual...
8
by: Martin | last post by:
I hope not, but, I think the answer to this question is "it can't be done". Northwind sample database. Orders form. Go to a new record. Select a customer in "Bill To:" Don't enter any...
1
by: Blue Lagoon Products - Customer Services | last post by:
Hi, We have an inhouse database that I designed in access 2000 with the help of all you guys some time ago. It stores orders and prints packing slips etc. I would like to put onto the packing...
2
by: jwolfley | last post by:
I'm a complete noob so excuse my ignorance. I'm looking for a simple drop down list on a form that limits the choices to foldernames that relate to the customer. I have three tables named...
5
imrosie
by: imrosie | last post by:
I need to import existing customer & order records into a new db. I need help with the best method of doing this. I tried, I've read forums, tutorials.....what I'm doing isn't working. I've tried...
1
by: chean | last post by:
The question Cik Mat operates bookstore. During their promotion period, there are books which are sold with special discounts, while others are sold at normal price. Write a program which...
6
by: TimberSales | last post by:
Hi I am attempting to create a pricelist to give to my customers, however I have become lost in the complexity of the query I am trying to perform and could really use some help. The database...
2
by: ruth m | last post by:
I have a Customer Details form which has two tabbed pages on it – one of which has a subform with a list of that particular customer’s orders on it. For some reason, when scrolling through the...
7
mseo
by: mseo | last post by:
hi, I have Follow-Up report for orders and deliveries to calculate the Build Quantities (the quantity I have to produce to fill the order) after subtracting the quantities delivered to the customer...
1
by: Joanne Campbell | last post by:
Hi Very new to Access 2007. I am creating this database for a company that takes orders over the phone. I do get a lot of repeat customers. I have created all my tables, Customers, Orders, Products,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.