We help IT Professionals succeed at work.

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

cres1121
cres1121 asked
on
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.
Comment
Watch Question

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
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".

Author

Commented:
I appreciate that..   I understand the problems just something we have to live with right now.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.