Checking for existence of data from a field

[litrelord]

First off, sorry for the subject. With my massive lack of knowledge in access I'm not really sure how I would best describe my question.

I have a query that shows two columns of a table. I want to show only the records where the same data (a 10 character text string) from column B is also in column A. However, the data won't be on the same row.

so if I had

A B
B C
C D
E F

Then I would want to show just the last two rows. I'm not bothered about the fact that "A" doesn't show in the second column. Just where there is data in the second column that's not in the first (e.g. "D" & "F" in the above).

Thanks for reading

Nick

 

[Norie]

Surely the rows you want to show are the last two.

I tried it with your data and this query

SELECT Query1.Field1, Query1.Field2
FROM Query1
WHERE (((Query1.Field1) In (SELECT Query2 From Query1)));

and it returned rows 2 & 3.

 

[blueshark]

Here's my guess at it. It assumes both the fields have the same data type. I try to explain it as detailed as possible since you said you're pretty new.

1. Create a query and added the source table twice.

2. Do a join between field1 (f1) of the table on the left and field2 (f2) of the table on the right.

3. Change the join type by right clicking on the join line. You'll want selection number two "Inlcude all records from the 'table on the right' and only those records from ' table on the left' where the the values are equal."
The result should be an arrow pointing from f2 to f1.

4. Put f1 from the table on the left (the one being pointed at) and f2 from the the other table in the design grid.

5. in the criteria section type Is Null

I think that gives you what I think you are looking for. You should have blanks in one column (because it's not there) and values in the other.

 

[litrelord]

The second example above (from blueshark) is perfect.

Thank you both

Nick