How to run this access query?

1
votes

Hey guys I'm looking to run this query/calculated field in Access, heres what I want to do:

If(Salaries.Amount > 20000) { 
    If(Salaries.Amount > 30000) {
        If(Salaries.Amount > 40000) {
            Permits.Band = "Band 4";
        } Else {
            Permits.Band = "Band 3";
        }
    } Else {
        Permits.Band = "Band 2";
    }
} Else {
    Permits.Band = "Band 1";
}

Salaries and Permits are tables, with another 2 tables in the DB - Cars and Staff.

Salaries/Staff are linked by Staff_ID: 1 to 1,
Staff/Permits are linked by Staff_ID: 1 to 1,
Permits/Cars are linked by Permit_ID: 1 to M.

Basically the "Band" field in Permits needs to be calculated depending on the staff members salary... The salary has got to be in a separate table from the staff details, hence the Salaries table...

Any ideas?

EDIT:

In response to answer 1:

SELECT Switch(Salaries.Amount > 40000, "Band 4",
              Salaries.Amount > 30000, "Band 3",
              Salaries.Amount > 20000, "Band 2",
              True, "Band 1") AS Band
FROM (Staff INNER JOIN (Permits INNER JOIN Cars ON Permits.Perm_ID = Cars.Perm_ID) ON Staff.Staff_ID = Permits.Staff_ID) INNER JOIN Salaries ON Staff.Staff_ID = Salaries.Staff_ID;

Gives "Type mismatch in expression" when run...

2
sqldatabasedatabase-designms-access

2 Answers

4
votes

See Access' help topic for the Switch Function.

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

"The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned."

SELECT Switch(Salaries.Amount > 40000, "Band 4",
              Salaries.Amount > 30000, "Band 3",
              Salaries.Amount > 20000, "Band 2",
              True, "Band 1") AS band
FROM [your tables expression];

Update: I built and tested the Switch statement against a Salaries table where the Amount field is a numeric data type. Unless your Amount field is not numeric, I would check your FROM clause. Does this query run without error?

SELECT *
FROM (Staff INNER JOIN (Permits INNER JOIN Cars
    ON Permits.Perm_ID = Cars.Perm_ID) ON Staff.Staff_ID = Permits.Staff_ID)
    INNER JOIN Salaries ON Staff.Staff_ID = Salaries.Staff_ID;
2
votes

If you want something a little less hard-coded, you could put the bands in a new table (columns: id/name, lower, upper).

The lowest "lower" should be zero, each subsequent "lower" should be exactly equal to the previous "upper", and the last "upper" should be some huge number to stand in for infinity.

Then join something like this:

select whatever
from salaries s
inner join band b on b.lower <= s.amount and b.upper > s.amount

If salaries.amount can be null then you'll have to use a left outer join and handle that case specially.