Access 2000 2 Field Lookup

[gino5555]

How do I get Access to automatically return a table value using 2 field critera? I'm assuming this is an SQL, but I can't figure out the coding. I need the query to return the % that an employee is vested based on the years of service (03 SY matching Vesting Schedule Column) so I can use it in another calculation in another query.

When I do this in Excel I use the VLookup and Match combined where the Match returns the column number and the VLookup then returns the %. I'm sure this is possible in Access, but can't figure it out. I'm not new to Access, but haven't used it in a few years and am a bit rusty.

The Vesting Schedule has the following fields:

Vest Name
1 (stands for 1 year of service - each year the % changes)
2 (2 years of service)
3 (etc to 10)

The service years are in a separate table linked via the Vest Name.

 

[gol4]

If I understand what you are trying to do here you will need to normalize your Vesting schedule table first. It will need to look like

Years percent
1 %
2 %
3 %
4 etc to 10

Then when you will join it to the other table based on service years field.

Hope that helps some.

 

[gino5555]

Thanks for the reply. It did help confirm my suspicions. I had nothing to do with the setup of the database, I'm coming in now and trying to do some "clean up" work. Normalization is just one of the many problems in this database. They have another database that's even worse. That one we're redoing from scratch. **SIGH**

Since there were only 10 variables I ended up doing a long IIF statment that ended up working just fine. I'll paste it below in case someone else runs into the same problem like I did...

Vest%: =IIf([Vest SY]=10,DLookUp("[10]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=9,DLookUp("[9]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=8,DLookUp("[8]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=7,DLookUp("[7]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=6,DLookUp("[6]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=5,DLookUp("[5]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=4,DLookUp("[4]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=3,DLookUp("[3]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=2,DLookUp("[2]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),
IIf([Vest SY]=1,DLookUp("[1]","[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),300))))))))))

 

[gol4]

Dlookup wants the field name as a sting couldn't you just convert it like this

Vest%: =IIf([Vest SY]<=10,DLookUp(cstr([Vest SY],"[VSch]","[Vest Name] ='" & [2003 Annual Report]![Vest Name] & "'"),300)

pretty sure it should work.

 

[gino5555]

I'll give it a shot.

I was told today that I have the go ahead to normalize the VSch table to day so instead of a separate field for the year I can have fields for [year] and [perc] so we can just use that as a criteria (yeah!).

My boss is already realizing that there will be some HUGE changes when I design the new database from scratch.