Finding last occurence of a record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I found a posting from lastyear which explained how to find the last
occurrence of a record using the date (Max) in an Access 2000 database. I
copied the sql code and tried to utilise it in my application however the
query seems to loop continuously finally hanging up access.I can't find the
original posting to confirm the original sql or contact the person who posted
it I have played around with it to no avail...any help would be
appreciated....

The Sql is as follows:
SELECT *
FROM [COLLECTION DATA] AS T
WHERE (((T.COLLECTION_DATE)=(SELECT Max(X.[COLLECTION_DATE])
FROM [COLLECTION DATA] AS X
WHERE X.SITE_ID = T.SITE_ID)));


Regards

Steve
 
Steve_s said:
Hi
I found a posting from lastyear which explained how to find the last
occurrence of a record using the date (Max) in an Access 2000 database. I
copied the sql code and tried to utilise it in my application however the
query seems to loop continuously finally hanging up access.I can't find the
original posting to confirm the original sql or contact the person who posted
it I have played around with it to no avail...any help would be
appreciated....

The Sql is as follows:
SELECT *
FROM [COLLECTION DATA] AS T
WHERE (((T.COLLECTION_DATE)=(SELECT Max(X.[COLLECTION_DATE])
FROM [COLLECTION DATA] AS X
WHERE X.SITE_ID = T.SITE_ID)));


Regards

Steve
Click to expand...

As far as I can see without knowing the table structures, your SQL looks
fine. Unfortunately, correlated subqueries like this perform abysmally in
Access: if there is a lot of data in your tables, it will take a VERY long
time to run - hours or even days is entirely possible!

You should, of course, make sure the relevant fields are indexed. If they
are, or if it doesn't help, then a bit of lateral thinking will be required.
For example, you could first run a make-table query to create a table
containing the maximum COLLECTION_DATE for every SITE_ID, index both fields
in the temporary table, and then use the temporary table in your query.
 
Another method is to use two queries.

Q1:
SELECT SiteID, Max(Collection_Date) as LastDate
FROM [Collection Data]
GROUP BY SiteID

Now, using that query join it, to the your table

SELECT [Collection Data].*
FROM [Collection Data] INNER JOIN Q1
On [Collection Data].Site_ID = Q1.SiteID AND
[Collection Data].Collection_Date = Q1.LastDate

This is often faster in Access than using the correlated subquery and you don't
need to build and populate a table.

Steve_s said:
Hi
I found a posting from lastyear which explained how to find the last
occurrence of a record using the date (Max) in an Access 2000 database. I
copied the sql code and tried to utilise it in my application however the
query seems to loop continuously finally hanging up access.I can't find the
original posting to confirm the original sql or contact the person who posted
it I have played around with it to no avail...any help would be
appreciated....

The Sql is as follows:
SELECT *
FROM [COLLECTION DATA] AS T
WHERE (((T.COLLECTION_DATE)=(SELECT Max(X.[COLLECTION_DATE])
FROM [COLLECTION DATA] AS X
WHERE X.SITE_ID = T.SITE_ID)));


Regards

Steve
Click to expand...

As far as I can see without knowing the table structures, your SQL looks
fine. Unfortunately, correlated subqueries like this perform abysmally in
Access: if there is a lot of data in your tables, it will take a VERY long
time to run - hours or even days is entirely possible!

You should, of course, make sure the relevant fields are indexed. If they
are, or if it doesn't help, then a bit of lateral thinking will be required.
For example, you could first run a make-table query to create a table
containing the maximum COLLECTION_DATE for every SITE_ID, index both fields
in the temporary table, and then use the temporary table in your query.
Click to expand...
 
Back
Top