substitute VLOOKUP that’s done in Excel

[funzone]

Hi, I am trying to substitute VLOOKUP that’s done in Excel for something that can be done in Access instead. As an example: I have several people who have several types of education, each individual might have 1-5 types of education. I do not need to see individuals that already have “ED” – education type. Since several people have several types of education, I cannot just eliminate “ED”, since the remainder of education type will appear in the results. I need to see everyone who doesn’t have “ED” education type. I am not sure what type of criteria I need to use here. Today, I create a VLOOKUP in Excel and I would like to change that going forward.
P.S I cannot change tables.
Let me know if you have any suggestions/questions.
Thanks.

[CJ_London]

the equivalent of vlookup for access is dlookup - see this link https://www.techonthenet.com/access/...in/dlookup.php

but usually in access you would use a query - without knowing what your tables look like, perhaps something like

SELECT tblPeople.*
FROM tblPeople LEFT JOIN (SELECT peopleFK FROM tblEducation WHERE EducationType="ED") AS hasED ON tblPeople.peoplePK=hasED.peopleFK
WHERE hasED.PeopleFK IS Null

but otherwise to assist you will need to provide details of your tables, some example data and the required outcome

[funzone]

Thanks Ajax. Dlookup won't work in this case. Everyone has an education type, none of the people have a null.
I already have a query created but it needs a criteria on the Education Type.
ID Education Type
12345 EDU
12345 CERT
12345 PCP
24568 EDU
24568 CERT
24568 PCP
78956 PCP
78956 CERT
78956 TEST

I need to only see ID 78956, since it doesn't have EDU.

[CJ_London]

I need to only see ID 78956, since it doesn't have EDU.

which is what my query does - suggest try it and see. If you have tried it, post the sql you actually used

[funzone]

See below. I appreciate any help.

SELECT TBL_EMP.EMP, TBL_EMPCODE.ED_TYPE
FROM TBL_EMP INNER JOIN TBL_EMPCODE ON TBL_EMP.EMP = TBL_EMPCODE.EMP;

[CJ_London]

just substitute the table and field names in my query for the ones in yours. Use my query since yours is nothing like what I suggested

e.g. replace tblPeople in my query with TBL_EMP, etc

[funzone]

Table Emp doesn't have Education Type ED
Maybe you can correct this in anyway.

Please see below:

SELECT TBL_EMP.EMP, TBL_EMPCODES.EDUC_TYPE
FROM TBL_EMPCODES LEFT JOIN TBL_EMP ON TBL_EMPCODES.EMP = TBL_EMP.EMP
WHERE TBL_EMPCODES.R_TYPE Like "ED" AS hasED ON TBL_EMP.EMP=hasED.EMP;

Thanks !