Vlookup in Access query, if values in table a exists in one or more places in table b

[sauko]

Hi,

What I am trying to do is quite simple, in excel at least so I have attached an Excel file where I show what I want to happen in access.
Here is a short explaination:

Field 1: Some of the (unique)values of a table called DDT_code will appear on one or several places in table DDT_Group_code, if it does appear on one or more places I want to mark it as "Yes" or something similar. Because then I know that it is a group mom.

Field 2: If the value of Field 1 is "Yes" then show the value of DDT_code (that means that DDT_Group_code for will always be blank), if the value of DDT_Group_Code and Field 1 is Blank the show the value of Sok_DDT.

Hmm this is quite dificult to explain even though it shouldnt be that difficult.
I have included an excel file which shows how the data flow should be, it is so easy to do in excel. I think the file should make it clear.

The thing is that I want to exlude All files (DDT_code) that is Not a Mom neither does it Have a Mom, In other words the files where both DDT_Group_code and then newly made field 1 is empty.


Thank you for your support, I am going crazy that I cant figure this one out.


Queries should not be a problem.

[rpeare]

The function you could use in access is the dlookup function which seems to be similar to vlookup, a word of warning though, dlookup functions are incredibly inefficient in queries and should not be used. as I understand it your starting dataset (in your existing query) consists of the left three rows. If There is a value in the third column [DDT Code] you want to see if that same code has any matching value anywhere in your field [DDT_Group_Code], if it does, put a "YES" in your fourth column otherwise leave it blank. You can only do this with a dlookup but I would strongly suggest you not do that. I suspect you can get around this using queries and subqueries but without an example of your database I can't really think of a way to tell you to proceed because I'm only seeing an example output of your query but now how the things are actually related.

[sauko]

Thank you for your quick response. After dinner and a beer I realized I was being quite stupid. Thank you for the explaination regarding dlookup, I have been trying it out a bit but couldnt get it to work.
I have not joined tables in a subquery and sorted out the values where two of the fields are similar code and group code. Then in the main query I use the subquery with the sorted out values and added a new field where I just added the two fields together with & since there will never be a possibility for both of the fields to be populated at the same time only the final mother group will be shown.
Hmm might not make much sense since I had a beer and Im really happy but now it works