HELP! Trying to make a query where every 5 orders a customer receives a discount. (1 Viewer)

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!

You can use Dcount() function to count record on your ordertable and if the result of the count in Mod 5 = 0 then you give a discount.

Here is a test done in a function to only display every 5th number:


JR

JANR,
sorry to be a pain but I'm relatively new to MS Access and VBA isn't one of my strengths.
Having said that, i'm not sure if what you posted is exactly what I wanted to do. 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:
(Click "DATABASE RELATIONSHIP" Attachment)
(bare in mind this is a school project)
Here are my queries:
(Click "DATABASE QUERIES" Attachment)
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:
(Click "DATABASE test data" Attachment)
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!

See attached mdb.

Its an over simplyfied db just to show the mechanics of the expressions. I'v added a Current count on the form so you can see when a customer gets his count to his fifth count and watch how the locked discount control displays the discount.

There is little VBA in the form exept in the Forms BeforeUpdate event where the discount is recorded in the table, default discount is zero.

Here is the expression behind the unbound discount control:

=IIf(Nz(DLookUp("CurrentCount","CurrentOrders","CustId=cboCustomer") Mod 5=0,0),[cboProduct].[column](2)*0,5*[NumberUnits],0)

The Nz marked in red is just there to ensure that you don't get an error if a customer dosen't have recorded any orders.

To keep track of the current count of order I used a query similar to what you have.

Hope this helps

JR

JANR,

Thanks man, I think I understand it its just a matter of applying that into my database now. It may take me some time but i'm sure i'll figure it out. If i can't and I have a problem i'll let you know.

Much appreciated G!

No problem Joe

You could also use DCount directly on the ordertable, and since DCount dosen't error on missing customer you don't need the Nz() wrapper function. It is a lot cleaner I think.

=IIf(DCount("*","tblOrder","CustFK=cboCustomer") Mod 5=0,[cboProduct].[Column(2)*0,5*[NumberUnits],0)

JR

Hey man,

I've come stuck! My discount field and Order Sum field just say "#NAME?".
I'm struggling to understand this DCount function. I'll research it a bit more in the meantime but if your free could you try taking a look at a version of my database that just consists of the tables and the relationships?

You don't have to, any extra help is appreciated! Gotta finish this database by friday night! its taken days off of my life already. Ima be so relieved when its complete and perfected.

See attached.

Added a form which uses DCount() to find who gets Discount, note the criteria parameter of the function which points to a form control called MyID that has its controlsource bound to the Customer ID field in the recordsource.

JR