subquery help, please! (1 Viewer)

[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

 

[Pat Hartman]

Instead of a subquery, try nesting three queries.

Query1:
Select ReferralId, Max(DateOfAction) As MaxDateOfAction
From YourTable
Group By ReferralId;

Query2:
Select First(t.PrimaryKey) As FirstPrimaryKey, q.ReferralId, q.MaxDateOfAction
From YourTable as t Inner Join Query1 as q on t.ReferralId = q.ReferralId AND t.DateOfAction = q.MaxDateOfAction
Group By q.ReferralId, q.MaxDateOfAction;

Query3:
Select *
From YourTable as t Inner Join query3 as q On t.PrimaryKey = q.FirstPrimaryKey;

Query1 gets the max date for a referralId.
Query2 gets the primary key of one of the records found by query1.
Query3 gets all the data for the record found by query2.

Of course all of this assumes that your table has a unique primary key.

 

[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.