mismatch problem

[mizzkk]

Hi all, my very first db..grovel, grovel, I hope that my question is coherent enough and not too stupid.




I have an Employee table. Each employee has 3 different billing rates named Rate1, Rate2 and Rate3. Which rate they use depends on the project they will be inputting hours for.
So I have a Project table which has an ID, Description and a BillingRate (Rate1, Rate2 or Rate3)
How do I set that up so that I can query for EmployeeName, ProjectID or description and the appropriate rate to create invoices?
In other words how do I get around the mismatch problem BillingRate being text and Rate1, Rate2 and Rate3 being numbers?
Is there a way to create criteria that compares a field name to a field value?

[June7]

The Employee table has the 3 numeric rate fields?

In query that joins Project and Employee tables, create a field with an expression:

Switch([BillingRate]="Rate1", [Rate1], [BillingRate]="Rate2", [Rate2], [BillingRate]="Rate3", [Rate3])

or

Choose(Right([BillingRate],1), [Rate1], [Rate2], [Rate3])


If BillingRate were a number field with values of 1, 2, 3:

Choose([BillingRate], [Rate1], [Rate2], [Rate3])

[mizzkk]

Super, thanks!