Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2018, 05:06 AM   #1
mantas123
Newly Registered User
 
Join Date: Apr 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
mantas123 is on a distinguished road
Post making a discount query based on repeated orders

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
Attached Images
File Type: png query.png (54.4 KB, 15 views)
File Type: png query 2.png (8.5 KB, 16 views)

mantas123 is offline   Reply With Quote
Old 04-17-2018, 05:16 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,445
Thanks: 92
Thanked 1,834 Times in 1,704 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
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.

Your choice. All will work.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-17-2018, 10:18 AM   #3
mantas123
Newly Registered User
 
Join Date: Apr 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
mantas123 is on a distinguished road
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

mantas123 is offline   Reply With Quote
Old 04-17-2018, 11:07 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,445
Thanks: 92
Thanked 1,834 Times in 1,704 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: making a discount query based on repeated orders

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-17-2018, 11:18 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
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)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 04-17-2018, 12:16 PM   #6
mantas123
Newly Registered User
 
Join Date: Apr 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
mantas123 is on a distinguished road
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
mantas123 is offline   Reply With Quote
Old 04-17-2018, 01:15 PM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,445
Thanks: 92
Thanked 1,834 Times in 1,704 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: making a discount query based on repeated orders

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


__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
HELP! Trying to make a query where every 5 orders a customer receives a discount. joe_madeira Queries 7 12-08-2011 11:49 PM
How to Show Results of Cost * Discount % Then Deduct The Discount In Same Text Box? MBM Forms 3 04-29-2011 05:35 PM
Making a form based on a query with a subform? NewShoes Queries 4 08-17-2009 09:19 AM
Customer Discount To Product Discount Group nexus2011 Tables 8 03-22-2007 04:52 AM
Making the Orders Form into a Web Page pmcleod Forms 2 06-10-2005 08:42 AM




All times are GMT -8. The time now is 05:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World