Multiple aggregates with fewest possible steps?

[Lord Sandwich]

Hi,

I have a table wherein only new data is provided by the user, and fields that have not changed from the previous record are left Null:

Date   Field1   Field2
1      B        (Null)
2      A        1
3      (Null)   2

Is there a simple way to produce the following results from an aggregate query:

Date   Field1   Field2
3      A        2

(Where [Date] is a parameter.)

Right now my process is pretty clumsy, requiring multiple steps to get the MaxOfDate for each Field, then each Field's value corresponding to its MaxOfDate. Not pretty.

 

[mresann]

give me a sec, sorry

 

[mresann]

Here's The Correct Query

Sorry, first query I posted was the original setup and not the finished one. Here is the finished query. I used "tblCheck" as the name of the table, otherwise kept your fieldnames.

Note the use of three subqueries and the use of an alias on the main query.

SELECT DISTINCT

(SELECT TOP 1 tblCheck.Date
FROM tblCheck
WHERE (((tblCheck.Date) Is Not Null))
ORDER BY tblCheck.Date DESC;
) AS MainDate, 

(SELECT TOP 1 tblCheck.Field1
FROM tblCheck
WHERE (((tblCheck.Field1) Is Not Null))
ORDER BY tblCheck.Date DESC;
) AS MainField1,

(SELECT TOP 1 tblCheck.Field2
FROM tblCheck
WHERE ((Not (tblCheck.Field2) Is Null))
ORDER BY tblCheck.Date DESC;) AS MainField2

FROM tblCheck AS tblCheck_1

 

[Lord Sandwich]

Whoa, that was quick! I did a quick fix to add a date parameter and it worked beautifully:

SELECT DISTINCT [Enter a date] AS MainDate, (SELECT TOP 1 tblCheck.Field1
FROM tblCheck
WHERE (((tblCheck.Field1) Is Not Null)) And tblCheck.Date <=[Enter a date]
ORDER BY tblCheck.Date DESC;
) AS MainField1, (SELECT TOP 1 tblCheck.Field2
FROM tblCheck
WHERE (((tblCheck.Field2) Is Not Null)) And tblCheck.Date <=[Enter a date]
ORDER BY tblCheck.Date DESC;
) AS MainField2
FROM tblCheck AS tblCheck_1;

Thanks mresann!