Created a query that needs to update a table before I start a process

I use a query to create a calculation but I need it to update a field in a particular table.  Before I run payroll I need to determine gross profit for 2014 and 2015.  I need it to update a field for each salesmen based on year.  This determines the percentage of commission they will get.   I have a query that will determine the gross profit based on year. How do I get it to update a table?  I don't know vb at all.  My query is qrytodeterminetotalgrossprofit and the field is sumofgrossprofit.  It also has employeeid, transyear  the table to be updated is tblcommissiontotal and the field i need updated is total sales.  I also have paidinyear and employeeid.  

Any help would be appreciated.
cres1121Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd suggest you NOT write that calculated value to the table, and instead use that query when you need to calculate the value. Since it has the EmployeeID value, you could JOIN that query to your Employee table, then output all the columns needed from each.

To do that, create a new query and add the query and the Employee table, Make sure they're related on the correct fields, and then create the query as you would any other query.

Then, use the new query when you do your "process".
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cres1121Author Commented:
I appreciate that..   I understand the problems just something we have to live with right now.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure I understand your response. Are you able to use the method I suggest? Quite honestly, it's the only non-code solution you'll find, I believe. You cannot create a query that calculates a value, and have that same query update another table. Access doesn't work like that.

You could do this in VBA, perhaps, depending on your table structure. You could run a query for each row in your "employee" table, and then run your other query to determine the commission, and then update the related row.

However, if your only goal is to get the commissionable amount for each employee and use that in another process, then the query method I suggest would do what you want.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.