subquery help, please!

[karatelung]

The SQL fragment below is the criteria for tblPlacement.DateOfAction. It filters records by ReferralID then returns only the Max(DateOfAction).

WHERE (([tblPlacement].DateOfAction)=(Select Max([DateOfAction]) FROM [tblPlacement] AS t_1 Where t_1.ReferralID = [tblPlacement].ReferralID))

This is great. The problem occurs with records that have a certain ReferralID with the same date. Say there are fifteen records where ReferralID = 3 and four of those have a Max(DateOFAction) = 4/1/2002. The query will return four records with ReferralID = 3. I want it to return only one record with ReferallID = 3. It doesn't matter which one.

Is there a way I can get the query to return records without the repeating ReferralID's?

Any information would be a huge help to me as I am stumped for the moment.

Thanks

 

[BLeslie88]

Did you try
Group By [tblPlacement].ReferralID
??
Regards...

 

[karatelung]

it was a consideration, but I need other fields that aren't part of an aggregate function.

this thought just occurred to me: nest another query within the existing subquery that will do the same thing as the existing subquery except use Max(AutoID). [AutoID] is the primary field for tblPlacement and is an AutoNumber field.

It sounds like it will work. I'll find out.

 

[RV]

What if you rebuild your WHERE clause using the IN operator:

WHERE (([tblPlacement].DateOfAction)IN(Select Max([DateOfAction]) FROM [tblPlacement] AS t_1 Where t_1.ReferralID = [tblPlacement].ReferralID))

By the way, you seem to have a repeating group (in case you aren't aware...)

Greetings,

RV

 

[karatelung]

RV, changing "=" to "IN" didn't change anything. If the Max(DateOfAction) is the same for three records that all have the same ReferralID, I'll still get all three records.

I think I need to somehow involve the indexed primary key [AutoID]. How would I put the following into the existing subquery to make a sub subquery? It seems like it would work, but I'm not sure.

WHERE (((tblPlacement.AutoID) IN (Select Max(AutoID) FROM tblPlacement AS t_2 Where t_2.ReferralID = t_1.ReferralID))

what exactly are "repeating groups"?

any insight or ideas are very much appreciated.

Thanks

 

[RV]

Karatelung,

your "problems" are definitely NOT in your WHERE clause.
I suspect you're using more conditions in your query which are causing your problems.
I also suspect your database is not normalized the way it could be.

>what exactly are "repeating groups"?<

Look here for more information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q100139

Or ask Pat (Hartman), it could be one of the subjects in the book she's writing.

Post the complete SQL statement.

Greetings,

RV

 

[karatelung]

Thank you. Thank you. Thank you, RV and Pat.

The nested queries is exactly what I needed. The people in this forum are incredibly helpful.

Thanks again.