Creating an "if then" in a query

[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.

 

[NG]

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