How to Count the "Nth" Number

[joe_madeira]

Hey,

Whats the expression used when you want to Count all the records (e.g. Count all the number of orders a customer has made) and for every 5th record ( so when the count =5,10,15,20...etc...) the field displays Yes.

In my case I have a Discount field of data type "Yes/No" and I want this field to display "Yes" when a customer has made 5 orders,10orders,15orders...etc.

Can this be done? what is the expression?

Appreciate the help!

 

[vbaInet]

Get a count and Mod it with 5.

IIF(Count([COLOR=Red]...[/COLOR]) Mod 5 = 0, "Yes", "No")

 

[joe_madeira]

Man it sounds so simple but for some reason I can't get it to work. It just says "No" even though one customer has made a total of 9 orders. surely on his 5 th order it should have displayed "Yes" in the discount field.

i don't know whether to put a discount field in the Order Item table or to just make a form out of the orders and then add a textbox with the control source set to the expression you gave me. Please help me out, ive been stuck on this for so long now.

 

[vbaInet]

It depends on how you wrote it. Show me what you wrote.

And the following statement doesn't make sense to me. Care to elaborate?

It just says "No" even though one customer has made a total of 9 orders. surely on his 5 th order it should have displayed "Yes" in the discount field.

 

[joe_madeira]

Well basically I've got these tables:

Customer
Order
Order Item
Menu

I've got test data in them all. So basically a customer makes an order for an order item and that order item is from the menu. (Restaurant System) The reason for the Order item table is because on order can consist of many items from the menu.

I made a query that consists of the customer table and the order table. In this query I have a totals expression. when i run the query it shows every customer and how many orders they have made. One customer has a total of 9 orders.

Ok, so ultimately what I'm trying to do is make a form whereby the user can make a new order for a customer. In this form I would like a total count to display on the side so that I know what order the customer is making. For example if the order is the customer's 3rd order...etc. Now every 5th Order a customer makes they get a 50% discount. How would I apply this? Do i make a field in one of the tables where it says "Yes" or "No" and then in the form I can put an If expression whereby if the Discount textbox says "yes" the discount of 50% will apply to the final price of the order.
OR do i not put a discount field in a table and just make a discount textbox in the form and work it out from there?

I hope u get what i'm trying to say

Oh and here is what I wrote:

I tried:
IIF(Count(Forms!Order!Order ID) Mod 5 = 0, "Yes", "No") = this was when i tried creating a new field in the order item table and I set the default value to this. but it came up with an error.
So i tried using the "Order ID" field in the Order Item table with this:
IIF(Count(Order ID) Mod 5 = 0, "Yes", "No") but no luck

I also tried making a query and then making a calculated field called "Discount:IIF(Count(Order ID) Mod 5 = 0, "Yes", "No")" but no luck either.

 

[gemma-the-husky]

it is really not easy to maintain a sequence like this in access

the point is that the sort order cannot be guaranteed - so labelling particaluar orders is potential not correct.

it is surely simpler to count the total orders (eg 10), and if the remainder after dividing by 5 is zero, you know you have just hit a 5th order....

 

[vbaInet]

I believe the expression is in post #2. I did mention that you should show me what you wrote?

Also, test it out with a DCount() function first.

 

[joe_madeira]

gemma-the-husky,

Where would i do that? In a form, in a query bearing in mind that i already have a query that counts all the number of orders a customer has made?
man you guys make it sound so eassssy! i'm so frustrated that i can seem to work this one out.

 

[joe_madeira]

I believe the expression is in post #2. I did mention that you should show me what you wrote?

Also, test it out with a DCount() function first.

Yeah sorry, I edited my 3rd post cause I remembered once i had posted

 

[joe_madeira]

I quickly made a cut down version of my database with just the tables and the totals query. If it's not too much to ask can anyone check it out and then add the discount function to the database in the best way possible. Thanks a bunch!

 

[gemma-the-husky]

depends what you want to do

possibly at the time you enter the order, count the total orders placed (within a certin period, if necessary) , and react accordingly.

 

[smig]

As gemma-the-husky said:
though it is possible, normaly you don't add a counter to the records. This is because when sort is changed the counter you created can go to trash.