I am converting several MS Excel spreadsheets into a single MS Access 2003 database.
The MS Excel VLOOKUP query I am trying to recreate is as follows:
=IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(EG2,EH:EI,2,FALSE))
New MS Access Table Name:
0301_ElectricitySupply_FeatureLine-up
Field names for MS Excel and MS Access are as follows:
MS Excel field: MS Access Field:
A2 Verify Config
“A2” is VLOOKUP function listed above
MS Excel field: MS Access Field:
B2 Config No
Example: B2 / Config No: MTU0301-0010
MS Excel field: MS Access Field:
EG2 Concatenated Config No_4
Example: EG2 / Concatenated Config No_4 cell data:
00200001000500010001000100010002000500010001000100030002000300010002000300010001000400040002000100010001000100020001
MS Excel field: MS Access Field:
EH Concatenated Config No_4_2
Note: Column “EH” was created as a copy of the “Concatenated Config No_4” field data.
MS Excel field: MS Access Field:
EI Config No_2
(Note: Column “EI” was created as a copy of the “Config No” field data)
The way this is SUPPOSED to work…………
If new “Config No” has unique “Concatenated Config No_4” data the “Verify Config” field should have “Good” recorded in that field.
However, if there is an existing “Config No” record that contains matching “Concatenated Config No_4” data the resulting “Verify Config” field should record the existing “Config No” instead of “Good” in the working form. And, if there is no “Concatenated Config No_4” data to compare for the newly entered “Config No” record, “No Data” should be recorded in the “Verify Config” field; MS Excel lists “#N/A” in “A” when this happens.
Side Note: If either the “Config No_2” or the “Concatenated Config No_4_2” are not necessary for the lookup and compare functions in MS Access I would like to delete them if possible to try and clean up unnecessary data in the table.
I hope this was not too complicated to understand. It had to be explained to me several times for me to understand how the VLOOKUP function was supposed to work.
Any assistance would be GREATLY appreciated!!!
Thanks,
Chip