Hi:

A subquery has me stumped. Suggestions appreciated...

Simplified sql is

Select Field1, Field2, (Select Max(FieldA) from tblEvent where
tblEvent.Field1 = Field1) as LastEvent, (Select FieldB from tblEvent
where tblEvent.Field1 = Field1 and tblEvent.FieldA = LastEvent) from
tblComponent where Field2 = "AAA"

Access is objecting to the tblEvent.FieldA = LastEvent in the second
subquery (specifically the "LastEvent" part)

how do I delimit the "LastEvent" criteria in the second subquery to
let Access know it should use the result of the first subquery as the
criteria?

In case I oversimplified, and you need to look at the actual SQL
statement, here it is:

SELECT dbo_tblComponent.FacilityID, dbo_tblComponent.TagNumber,
IIf([CompFrequency]="Exempt","Exempt","Not Exempt") AS CompStatus,

(select max(EventDate) from dbo_tblEvents where
((dbo_tblEvents.FacilityID = facilityID and
dbo_tblEvents.EventTagNumber = tagnumber) and (dbo_tblEvents.EventType
= "Startup" or dbo_tblEvents.EventType = "In Service" or
dbo_tblEvents.EventType = "Shutdown" or "Out of Service" or
dbo_tblEvents.EventType = "TOS" ))) AS LastStatusDate,

(select EventType from dbo_tblEvents where dbo_tblEvents.facilityID =
FacilityID and dbo_tblEvents.EventTagNumber = TagNumber and
dbo_tblEvents.EventDate = (LastServiceDate)) AS compActive

FROM dbo_tblComponent
WHERE (((dbo_tblComponent.FacilityID)=1))
ORDER BY dbo_tblComponent.FacilityID, dbo_tblComponent.TagNumber;

Thanks

On Jan 11, 8:37*pm, Tom <rtmn... (AT) swbell (DOT) net> wrote:
Why not make your statement
Select Max(FieldA) from tblEvent where > tblEvent.Field1 = Field1)
as LastEvent
it's own separate query. Group on the key field and get the max value
for each keyfield. I think that would make your life simpler and it
would run faster.

On Wed, 11 Jan 2012 18:37:47 -0800 (PST), Tom <rtmnews (AT) swbell (DOT) net>
wrote:

SQL is weird about accessing defined fields by name. You might
have to expand
LastEvent
into its definition (the first subquery) and hope the optimiser will
recognise the commonality.

Or could you break it up into two queries?
select Field1, Field2, ... as LastEvent
from tblComponent
where ...

select Field1, Field2, LastEvent, <second subquery>
from FirstQueryResults

[snip]

Sincerely,

Gene Wirchenko

Thanks all for suggestions.

After a little more research, I decided to try a slightly different
approach.


SELECT dbo_tblComponent.FacilityID, dbo_tblComponent.TagNumber,
dbo_tblComponent.CompFrequency, dbo_tblEvents.EventDate,
dbo_tblEvents.EventType
FROM [SELECT dbo_tblEvents.FacilityID, dbo_tblEvents.EventTagNumber,
Max(dbo_tblEvents.EventDate) AS LastServiceDate
FROM dbo_tblEvents
WHERE (((dbo_tblEvents.EventType)="Startup" Or
(dbo_tblEvents.EventType)="In Service" Or
(dbo_tblEvents.EventType)="Shutdown" Or
(dbo_tblEvents.EventType)="TOS" Or (dbo_tblEvents.EventType)="Out of
service") AND ((DateDiff("d",[dbo_tblEvents].
[EventDate],#1/1/2010#))>=0))
GROUP BY dbo_tblEvents.FacilityID, dbo_tblEvents.EventTagNumber]. AS
MyQuery, dbo_tblComponent INNER JOIN dbo_tblEvents ON
(dbo_tblComponent.FacilityID = dbo_tblEvents.FacilityID) AND
(dbo_tblComponent.TagNumber = dbo_tblEvents.EventTagNumber)
WHERE (((dbo_tblEvents.EventDate)=[myquery].[LastServiceDate]) AND
((dbo_tblEvents.FacilityID)=[myquery].[facilityID]) AND
((dbo_tblEvents.EventTagNumber)=[myquery].[eventtagnumber]));

Works like a charm, and is significantly faster than what I was
previously fooling with.