Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-17-2008, 11:16 AM   #1
oZone
Registered User
 
Join Date: Oct 2008
Posts: 103
Thanks: 0
Thanked 0 Times in 0 Posts
oZone is on a distinguished road
Question Update query - calcualte a percentage input by user?

Hi, I'm new to MS Access and have a question about Parameter queries.

I have a "Parts" database that contains part numbers, descriptions, and prices and I'm trying to create an Update Query that will prompt the user for a part number, then prompt them for a percentage to increase or decrease a part by, then update the part price in the Parts table. But I'm having some trouble with the math and getting Access to do what I want it to do...

I'm editing the query in design view and I've entered two fields in the query, "PartNumber" and "Price" from the Parts table. I know how to prompt the user to enter a value, by using brackets [ ], but unsure how to format the percentage calculation.

So far I have this in the "Update To" Price field:
[Price]*([Enter a percentage:]%)

But keep getting an error because I'm not doing something rite...

Here is an example of what I am trying to achieve: I'd like the query to prompt the user first for the part number, then for a percentage to increase or decrease the part price by.
The prompt should say "Please enter a percentage:" then the user would enter "10" to increase the price by 10% or "-10" to decrease the price by -10%. Then the query would multiply the part price by what the user entered and update the Part table accordingly.

I'd like the prompt to be simple, E.G. rather than requiring the user to enter "1.05" to increase by 5%, just have the user enter "5" for a 5% increase.


Any help would be greatly appreciated,
Thanks!

oZone is offline   Reply With Quote
Old 10-17-2008, 11:41 AM   #2
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Re: Update query - calcualte a percentage input by user?

Quote:
Originally Posted by oZone View Post
Hi, I'm new to MS Access and have a question about Parameter queries.

I have a "Parts" database that contains part numbers, descriptions, and prices and I'm trying to create an Update Query that will prompt the user for a part number, then prompt them for a percentage to increase or decrease a part by, then update the part price in the Parts table. But I'm having some trouble with the math and getting Access to do what I want it to do...

I'm editing the query in design view and I've entered two fields in the query, "PartNumber" and "Price" from the Parts table. I know how to prompt the user to enter a value, by using brackets [ ], but unsure how to format the percentage calculation.

So far I have this in the "Update To" Price field:
[Price]*([Enter a percentage:]%)

But keep getting an error because I'm not doing something rite...

Here is an example of what I am trying to achieve: I'd like the query to prompt the user first for the part number, then for a percentage to increase or decrease the part price by.
The prompt should say "Please enter a percentage:" then the user would enter "10" to increase the price by 10% or "-10" to decrease the price by -10%. Then the query would multiply the part price by what the user entered and update the Part table accordingly.

I'd like the prompt to be simple, E.G. rather than requiring the user to enter "1.05" to increase by 5%, just have the user enter "5" for a 5% increase.


Any help would be greatly appreciated,
Thanks!

I think all you might need is to set the "Update To" Price field to the something like:

Code:
OldPrice+OldPrice*[Enter a Percentage:]
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Old 10-17-2008, 11:49 AM   #3
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 539 Times in 521 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Update query - calcualte a percentage input by user?

wont that be
OldPrice+OldPrice*[Enter a Percentage:]/100

if you want the user to enter 10 for 10%

Brian

__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 10-17-2008, 11:52 AM   #4
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Re: Update query - calcualte a percentage input by user?

Quote:
Originally Posted by Brianwarnock View Post
wont that be
OldPrice+OldPrice*[Enter a Percentage:]/100

if you want the user to enter 10 for 10%

Brian
It would depend on whether a 6% increase would be entered as 6 or 0.06. oZone will need to decide that.
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Old 10-17-2008, 11:54 AM   #5
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 539 Times in 521 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Update query - calcualte a percentage input by user?

The op specifically stated that it would be 6

Quote:
I'd like the prompt to be simple, E.G. rather than requiring the user to enter "1.05" to increase by 5%, just have the user enter "5" for a 5% increase.
Don't get uptight you do a good job but we all slip up on the user requirements from time to time.

Brian
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 10-17-2008, 11:56 AM   #6
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Re: Update query - calcualte a percentage input by user?

Quote:
Originally Posted by Brianwarnock View Post
The op specifically stated that it would be 6



Don't get uptight you do a good job but we all slip up on the user requirements from time to time.

Brian
Good catch
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Old 10-19-2008, 11:08 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Update query - calcualte a percentage input by user?

i think after you get the input from the user, you should validate that it is an expected range, because of the presentation issues you describe

gemma-the-husky is offline   Reply With Quote
Old 10-19-2008, 03:36 PM   #8
oZone
Registered User
 
Join Date: Oct 2008
Posts: 103
Thanks: 0
Thanked 0 Times in 0 Posts
oZone is on a distinguished road
Re: Update query - calcualte a percentage input by user?

I see, thanks for all the help.
oZone is offline   Reply With Quote
Old 10-20-2008, 04:01 AM   #9
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 539 Times in 521 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Update query - calcualte a percentage input by user?

Quote:
Originally Posted by gemma-the-husky View Post
i think after you get the input from the user, you should validate that it is an expected range, because of the presentation issues you describe
I think that you will have to help me here Gemma, if you are just running a query how do you do that without a bunch of IIfs, especially as the user is permmitted to enter 5 or 5%.

Brian
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 03-14-2013, 10:38 AM   #10
hernandez0206
Newly Registered User
 
Join Date: Mar 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
hernandez0206 is on a distinguished road
Unhappy Re: Update query - calcualte a percentage input by user?

I need help with my project I need help with my queries update the MonthlyFee field by increasing it by 10% thank you.
hernandez0206 is offline   Reply With Quote
Old 08-09-2019, 02:11 AM   #11
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
How to enter dates from vba / query

Hello friends

I want to make a report which display data as below
Column 1 Jan to till today.
Column 2 Last 30 days like 16 Jul to 15 Aug.

This report is completed and all data is being displayed... see attachment

But I have to mention 16 Jul to 15 Aug manually.
How can computer write itself this sentence automatically.

Kindly guide me ...

Thanks
irsmalik
Attached Images
File Type: jpg REPORT.jpg (62.9 KB, 6 views)
irsmalik is offline   Reply With Quote
Old 08-09-2019, 02:29 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,407
Thanks: 162
Thanked 1,730 Times in 1,700 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Update query - calcualte a percentage input by user?

This is a 6 year old unrelated thread!

I would suggest starting a new one or get a mod to move it for you.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

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.
Minty is offline   Reply With Quote
Old 08-09-2019, 03:00 AM   #13
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Update query - calcualte a percentage input by user?

Dear Minty

6 years ago I was member of this forum... so how could I know that this topic has discussed. I will as the question that I need.
If this is a knowledge base forum... .then you must help me by solving my problem...
your remarks are not appreciable.

irsmalik
irsmalik is offline   Reply With Quote
Old 08-09-2019, 03:05 AM   #14
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,407
Thanks: 162
Thanked 1,730 Times in 1,700 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Update query - calcualte a percentage input by user?

If you look carefully the last posting date is at the top of every post.

Your report would need to be based on two queries one to produce the current data, and one to produce the total to date. These could then be joined onto the reports data source as a third query.

Start by making two queries that do the first parts then join them to produce the third part. If you get stuck postup some sample data and I'm sure we can assist further.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

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.
Minty is offline   Reply With Quote
Old 08-09-2019, 03:07 AM   #15
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,393
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Update query - calcualte a percentage input by user?

if you can share your db with us, we will see what we can do.
just mention which table to work.
__________________
"Never stop learning, because life never stops teaching"
arnelgp 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
Accept User Input for a Query praveen_khm Queries 5 07-10-2008 02:50 AM
Wildcard query with User Input Parameter Not Working shewolf Queries 1 07-12-2007 01:58 PM
Query : Group by & Sum Total with user input? Demoniak Queries 5 03-18-2007 04:58 PM
Passing parameter to a query from within a report (not user input) nelly Reports 2 10-19-2004 07:59 AM
[SOLVED] Storing the user input of a parameter query. skhan20 General 2 08-08-2002 04:11 PM




All times are GMT -8. The time now is 08:55 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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World