|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 9th, 2010, 04:56 PM
posted to microsoft.public.access
|
|||
|
|||
Alternative for MS Excel VLOOKUP function in MS Access2003
I am converting several MS Excel spreadsheets into a single MS Access 2003
database for a User. The User’s original MS Excel VLOOKUP query is coded as follows: =IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(E G2,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 Cell: MS Access Field: A2 Verify Config “A2” is VLOOKUP function listed above. MS Excel Cell: MS Access Field: B2 Config No Example: B2 / Config No: MTU0301-0010 Note: This number is a manually created unique number based on specific groupings. MS Excel field: MS Access Field: EG2 Concatenated Config No_4 Example: EG2 / Concatenated Config No_4 cell data: 00200001000500010001000100010002000500010001000100 03000200030001000200030001000100040004000200010001 0001000100020001 MS Excel field: MS Access Field: EH Concatenated Config No_4_2 Note: Column “EH” was created as a copy of column “EG”. Subsequently, the “Concatenated Config No_4_2” field is a copy of the “Concatenated Config No_4” field. MS Excel field: MS Access Field: EI Config No_2 Note: Column “EI” was created as a copy of the column “Config No”. Subsequently, the “Config No_2” field is a copy of the “Config No” field. 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. 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. 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; in its VLOOKUP function, MS Excel lists “#N/A” in column “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 clean up a lot of 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 
|
|
#2
March 9th, 2010, 06:10 PM
posted to microsoft.public.access
|
|||
|
|||
|
Alternative for MS Excel VLOOKUP function in MS Access2003
Check out DLookup in Help.
-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "CBender" wrote: I am converting several MS Excel spreadsheets into a single MS Access 2003 database for a User. The User’s original MS Excel VLOOKUP query is coded as follows: =IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(E G2,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 Cell: MS Access Field: A2 Verify Config “A2” is VLOOKUP function listed above. MS Excel Cell: MS Access Field: B2 Config No Example: B2 / Config No: MTU0301-0010 Note: This number is a manually created unique number based on specific groupings. MS Excel field: MS Access Field: EG2 Concatenated Config No_4 Example: EG2 / Concatenated Config No_4 cell data: 00200001000500010001000100010002000500010001000100 03000200030001000200030001000100040004000200010001 0001000100020001 MS Excel field: MS Access Field: EH Concatenated Config No_4_2 Note: Column “EH” was created as a copy of column “EG”. Subsequently, the “Concatenated Config No_4_2” field is a copy of the “Concatenated Config No_4” field. MS Excel field: MS Access Field: EI Config No_2 Note: Column “EI” was created as a copy of the column “Config No”. Subsequently, the “Config No_2” field is a copy of the “Config No” field. 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. 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. 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; in its VLOOKUP function, MS Excel lists “#N/A” in column “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 clean up a lot of 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
|
|
#3
March 9th, 2010, 08:56 PM
posted to microsoft.public.access
|
|||
|
|||
|
Alternative for MS Excel VLOOKUP function in MS Access2003
Jerry,
I tried working with the DLookup function but cannot get the desired results I am looking for. The problem I am running into is this..... If the new “Config No” being entered has a unique “Concatenated Config No_4” data the “Verify Config” field should have “Good” recorded. However, if there already exists a “Config No” record containing identical “Concatenated Config No_4” data, the “Verify Config” field should show the assotiated “Config No” of the existing “Concatenated Config No_4” instead of “Good” in the working form. If there is no “Concatenated Config No_4” data to compare for the newly entered “Config No” record (all of the concatenated fields are blank), “No Data” should be shown in the “Verify Config” field; in the MS Excel VLookup function “#N/A” is shown in column “A” when this happens. I cannot code the query properly to search through the existing “Concatenated Config No_4” for a matching record and show the associated “Config No” if the data matches an existing record, “Good” if the new “Concatenated Config No_4” is unique, or “No Data” if there is no new “Concatenated Config No_4” data to perform a search on. -- Chip "Jerry Whittle" wrote: Check out DLookup in Help. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "CBender" wrote: I am converting several MS Excel spreadsheets into a single MS Access 2003 database for a User. The User’s original MS Excel VLOOKUP query is coded as follows: =IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(E G2,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 Cell: MS Access Field: A2 Verify Config “A2” is VLOOKUP function listed above. MS Excel Cell: MS Access Field: B2 Config No Example: B2 / Config No: MTU0301-0010 Note: This number is a manually created unique number based on specific groupings. MS Excel field: MS Access Field: EG2 Concatenated Config No_4 Example: EG2 / Concatenated Config No_4 cell data: 00200001000500010001000100010002000500010001000100 03000200030001000200030001000100040004000200010001 0001000100020001 MS Excel field: MS Access Field: EH Concatenated Config No_4_2 Note: Column “EH” was created as a copy of column “EG”. Subsequently, the “Concatenated Config No_4_2” field is a copy of the “Concatenated Config No_4” field. MS Excel field: MS Access Field: EI Config No_2 Note: Column “EI” was created as a copy of the column “Config No”. Subsequently, the “Config No_2” field is a copy of the “Config No” field. 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. 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. 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; in its VLOOKUP function, MS Excel lists “#N/A” in column “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 clean up a lot of 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
|
|
#4
March 13th, 2010, 05:34 PM
posted to microsoft.public.access
|
|||
|
|||
|
Alternative for MS Excel VLOOKUP function in MS Access2003
"CBender" wrote in message ... I am converting several MS Excel spreadsheets into a single MS Access 2003 database for a User. The User’s original MS Excel VLOOKUP query is coded as follows: =IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(E G2,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 Cell: MS Access Field: A2 Verify Config “A2” is VLOOKUP function listed above. MS Excel Cell: MS Access Field: B2 Config No Example: B2 / Config No: MTU0301-0010 Note: This number is a manually created unique number based on specific groupings. MS Excel field: MS Access Field: EG2 Concatenated Config No_4 Example: EG2 / Concatenated Config No_4 cell data: 00200001000500010001000100010002000500010001000100 03000200030001000200030001000100040004000200010001 0001000100020001 MS Excel field: MS Access Field: EH Concatenated Config No_4_2 Note: Column “EH” was created as a copy of column “EG”. Subsequently, the “Concatenated Config No_4_2” field is a copy of the “Concatenated Config No_4” field. MS Excel field: MS Access Field: EI Config No_2 Note: Column “EI” was created as a copy of the column “Config No”. Subsequently, the “Config No_2” field is a copy of the “Config No” field. 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. 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. 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; in its VLOOKUP function, MS Excel lists “#N/A” in column “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 clean up a lot of 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
|
|
#5
March 17th, 2010, 01:12 PM
posted to microsoft.public.access
|
|||
|
|||
|
Alternative for MS Excel VLOOKUP function in MS Access2003
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
