making a discount query based on repeated orders (1 Viewer)

Hello community, I am trying to make a calculated field that would give me a discount on repeated orders based on multiple I already have done a discount on large orders
here is the statements in my calculated fields:
Order Cost: [Quantity]*[Product Cost]+[fixed Delivery Cost]
Discount: IIf(([Order Cost])>100,([Order Cost])*0.1,0)
Total cost: [Order Cost]-[Discount]

Thanks in advance

Welcome to the forum
You have three choices

1. In your query field for discount, replace order cost with the formula used to calculate it.
Similarly with order cost and discount in the total cost field
2. Do each step as a separate query to ensure these are calculated in turn
3. Create functions to calculate each part and use those in your query.

Your choice. All will work.

am not sure I quite understood you there I am trying to add another calculated field which would give a 20% discount if a customer has made multiple orders.
I believe what you have told me is how to tidy up my 3 calculations into one for a discount on a large order

Ah yes. Sorry about that.

I thought you couldn't get your query to work so suggested ways of doing so.
If it does work now without parameter popups then I'm surprised.

As for the repeated orders question you actually meant, you will need to provide more information such as how many orders and in what timescale are eligible for that 20% discount. If eligible, is that for any size order?
Also does the large order discount still apply?

Whatever your answers to questions like that, I think you will need a vba procedure which basically says

If customer eligible for repeat order discount, then
......run a query or some code
Else if eligible for large order discount then
....run the original query or some other code
Else
....no discount applies
End if

I am trying to add another calculated field which would give a 20% discount

Click to expand...

to be clear, you mean a calculated column in a query? or a calculated control in a form? or a calculated field in a table?

Describe your calculation. Your description refers to a multiple but then not used.

At the moment best I can suggest is using the dcount function

DiscountPC:iif(dcount("*","tblOrders","CustID=" & custID)>3, 0.2, 0)

I basically want an if statement that says something like this: if customerID is repeated 2 or more times then discount = 20%
there is no timescale or sizes this is not for a real business it is a school project so it doesn't have to be complex

In that case, just combine the ideas from my post & CJ London's

As it's a school project, it is important that you develop the final solution from here