Queries based upon varying factors inside the records

[Brettrb]

Assume I have a database with a [Table1]. This [Table1] has data in 4 fields: [Date1], [Date2], [Value1], [Value2]. I want to create a query using SQL Statements that will do varying things based upon values in the records.

i.e. Let's say there is values in all 4 fields. Since the [Date2] field stands for the most recent date, I only want to apply a criteria to the most recent value, correspondingly, [Value2]. Based upon the records (some have an entry in [Date2] and [Value2] and some only have an entry in the first two: [Date1] and [Value1]) I want to apply criteria to either the [Value1] or [Value2] field, but not both. Is there a way I can first have the coding look at both the [Date1] and [Date2] fields to see if there is an entry in one or the other, and then apply a criteria to the corresponding [Value1] or [Value2].

I know this sounds confusing, so if you have any questions or can provide some help to me, please feel free to e-mail me about the subject.

Thanks a lot.

Brett Beckett
[email protected]

[JHausmann]

Union statement.

Select * from table where criteria applies to Date1/Value1
UNION ALL
Select * from table where criteria applies to Date2/Value2

[This message has been edited by JHausmann (edited 08-09-1999).]

[This message has been edited by JHausmann (edited 08-09-1999).]