Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Queries (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=8)
-   -   making a discount query based on repeated orders (https://www.access-programmers.co.uk/forums/showthread.php?t=299313)

 mantas123 04-17-2018 05:06 AM

making a discount query based on repeated orders

2 Attachment(s)
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]

Re: making a discount query based on repeated orders

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.

 mantas123 04-17-2018 10:18 AM

Re: making a discount query based on repeated orders

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

Re: making a discount query based on repeated orders

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

 CJ_London 04-17-2018 11:18 AM

Re: making a discount query based on repeated orders

Quote:
 I am trying to add another calculated field which would give a 20% discount
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)

 mantas123 04-17-2018 12:16 PM

Re: making a discount query based on repeated orders

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