Creating an "if then" in a query

  • Thread starter Jack at ArcLight
  • Start date


Jack at ArcLight

Hi All:

I have a number value for an investment db that corresponds to the
maintenance priority of that investment (priority 1,2 and 3). The higher
priority, the more attention that needs to be paid to the investment.

HR has a method of assigning a workload score to an investment based on its
priority: 1= workload score of 5, 2=3 and 3=1.

Can I create a query that will automatically assign these values to total
for a total workload score without having a separate field to enter the
workload value?

Hope this makes sense.




Hi Jack:

try the function "choose" syntax: see Access help or help within the
function builder.



John Spencer MVP

I would create a small table WorkLoadLevel with two fields -
MaintenancePriority (same type of field as maintenance priority in your
current table) and WorkloadScore (Number field)

Then all you need to do is join the current table and this new table together
in your queries and you will automatically have the WorkLoadScore available
for use. Even better if another priority gets added or the workloadScore
changes for a particular priority, you make the change in this table's records
and it will automatically flow to all your queries and reports.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linking forms 1
auto assign project 9
Ranking by Query 4
automatically assign value 2
Create a query 7
total Query 1
Query using two different date values 5
Creating a list from Table Data 4