Ok – subqueries consistently kick my a**. Any help would be much
appreciated.

I have a table tblEvents with:
EventID – autonumber ID field
EventTagNumber – FK from another table
EventDate
EventType
Etc

I have a query qryEndingEvent that returns all records from the
tblEvents prior to a given date specified by the user (basically its
so I don’t have to keep remembering to put the ending event date in
any queries that reference tblEvents – I just use the qryEndingEvent
everywhere)

So here is what I want… for each EventTagNumber I want returned all
the fields from tblEvent for the latest date where EventType = ‘foo’
or ‘foo2’

My effort was:

SELECT tblEvents.EventID, tblEvents.EventTagNumber,
tblEvents.EventDate, tblEvents.EventType
FROM tblEvents
WHERE

(((tblEvents.EventID) In (SELECT TOP 1 EventID FROM qryEndingEventDate
WHERE EventTagNumber = '" & tblEvents.EventTagNumber & "' AND
(EventType='foo' Or EventType=’foo2’) ORDER BY EventDate DESC)));

I got back an empty recordset…

Thanks

Update:

I changed the query to read:

SELECT qryEndingEventDate.EventID, qryEndingEventDate.EventTagNumber,
qryEndingEventDate.EventDate, qryEndingEventDate.EventType
FROM qryEndingEventDate
WHERE (((qryEndingEventDate.EventID) In (SELECT TOP 1 dupe.EventID
FROM qryEndingEventDate as dupe WHERE dupe.EventTagNumber =
qryendingeventdate.EventTagNumber AND (dupe.EventType='foo' Or
dupe.EventType='foo2') ORDER BY dupe.EventDate DESC)));

and it worked... happy dance, happy dance...

Now the problem is that its dog-awful slow. The db has about 7000
TagNumbers and the response time is worse than the class module I had
tried...

Suggestions would be much appreciated...

Since both the main query and the subquery seem to be using
qryEndingEventDate as source, you are running a query into a query that, for
every record must run a subquery into a query. If that's too slow, you could
use a temporary table to solve the problem.

That is, turn qryEndingEventDate into an Append query (or Make Table
initially), with suitable indexes on the criteria fields. Then use this
table as the source for the final query instead of qryEndingEventDate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Tom" <rtmnews (AT) swbell (DOT) net> wrote

Allen:

Thanks for the suggestion. I tried removing qryEndingEventDate from
the equation just to see if that was really slowing me down that much
by going back to tblEvents for both the query and the subquery. It
speed things up a little, but not enough to make a practical
difference. I'll fool around some with building stacked queries to
see if that helps, but otherwise I guess its a temp table.

Tom

On Oct 17, 7:47*pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) invalid> wrote: