comparing fields in same recordset

Hi,

Could anyone tell me how I would write an SQL statement to retrieve records based on matching data two common fields (from different records) in the same recordset? For instance:

Select * from table where anyrecordfield1 = anyrecordfield4

then perform functions on the two records based on their common field names to achieve a single record?

Thanks.

Hi,

As for the first part of the question you might mean something along these lines?:


SELECT t1.Field1, t1.Field2
FROM Table1 AS t1 INNER JOIN
Table1 AS t1bis ON t1.Field1 = t1bis.Field4


>then perform functions on the two records based on their common field names to achieve a single record?

this part I don't quite follow.


Regards,
Shurik

Thanks! I think I'm headed in the right direction now, except this is complicated by the fact I'm selecting criteria from two tables. Is it possible to alias t1 and t2 (tables I'm selecting from) as a single table? Also, I'm getting "Join Expression Not Supported" ??
Thanks again.

OK...I caught my mistake.

My next question would be...how do I select all the remaining records? (The ones not returned - I need this in a separate recordset).

Thanks