Query - How to add a column that uses a formula

Hi,

I am not sure this is possible, but hoping that someone might be able to work out a method!

I am using a query to pull information from multiple sources and arrange it in a column order suited for a mass import in another program. It is for the calculation of pay for staff members. Is it possible to add a column to the query, which looks at the hours for each employee from the query, then multiplies that by their rate of hourly pay in another tab to give a column for each employee titled "Pay". I would be able to do this for a normal set of data, but im fairly new to queries, so not quite sure if its possible / how to go about it.

I have attached a version below. However I have had to delete the query as its contains sensitive data, I trust the attachment will still help.

I hope that makes sense, if not please let me know and I will happily try to explain in another way.

Is this what you wanted:

=VLOOKUP(A2,'List of ALL Staff and Pay'!$A$2:$B$9,2,FALSE)*C2

copied down...

Hi

Maybe the following macro that runs immediately after the Data Query

Please Login or Register  to view this content.

First off - you haven't deleted the query, and all the sensitive data is still there. You may wish to delete the file attachment.

Second - you can easily add your "Pay Rate" table as a data query, and merge with your existing query.

Third - you probably want to take the Site / Site Code lookup out of that monstrous "if then else" statement, and put it in a separate table, much like your pay rate table.

After all that, your query looks something like this (source name removed, to protect your sensitive data):

Please Login or Register  to view this content.

Note that you'll need to add queries named tbRates and tbSites which simply look at tables on your "List..." worksheet:

Please Login or Register  to view this content.