J
jschmuck9
Hi -
I have database with Contracts table and ProgramID table and Rates Table.
The ProgramID and Rates table are linked by primary keys so easy for me to
query if ProgramID = A, then find Rate A.
The Contracts table comes from 3rd party source however, and uses customer's
contract # as primary key. This key doesn't relate to ProgramID in anyway.
What fields are in common though are Contract Term, Contract Length, and
Contract Deductible. So I tried to use query where criteria would match off
the apprioriate ProgramID for each Contract #.
The problem I have is that customers contracts can have a decreased
deductible for a fee. Creates a problem because now when I try to match off,
I might have contract with Term=50, Length = 3, Deductible = 50, but the
match fails because Should be 50/3/100. This is only an issue with Contract
Type "A". No other types (B,C,D) have an option for decreased deductible.
How can I make an IIF statment in WHERE clause or Access Query builder such
that "IFF([Contracts]![Type])<>"A",[contracts]![term]=[programid]![term] And
[contracts]![length]=[programid]![length] And
[contracts]![deductible]=[programid]![deductible],[need to insert code to
force it to treat deductible as 100 even if it shows 0 or 50])
I even tried creating an expression DeductibleFake: inserted an iif
statement, but it didn't seem to achieve what I needed.
Hopefully i didnt provide too much detail that it was rambling.
Any help would be appreciated - Jason
I have database with Contracts table and ProgramID table and Rates Table.
The ProgramID and Rates table are linked by primary keys so easy for me to
query if ProgramID = A, then find Rate A.
The Contracts table comes from 3rd party source however, and uses customer's
contract # as primary key. This key doesn't relate to ProgramID in anyway.
What fields are in common though are Contract Term, Contract Length, and
Contract Deductible. So I tried to use query where criteria would match off
the apprioriate ProgramID for each Contract #.
The problem I have is that customers contracts can have a decreased
deductible for a fee. Creates a problem because now when I try to match off,
I might have contract with Term=50, Length = 3, Deductible = 50, but the
match fails because Should be 50/3/100. This is only an issue with Contract
Type "A". No other types (B,C,D) have an option for decreased deductible.
How can I make an IIF statment in WHERE clause or Access Query builder such
that "IFF([Contracts]![Type])<>"A",[contracts]![term]=[programid]![term] And
[contracts]![length]=[programid]![length] And
[contracts]![deductible]=[programid]![deductible],[need to insert code to
force it to treat deductible as 100 even if it shows 0 or 50])
I even tried creating an expression DeductibleFake: inserted an iif
statement, but it didn't seem to achieve what I needed.
Hopefully i didnt provide too much detail that it was rambling.
Any help would be appreciated - Jason