Access97, delete oldest record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access97, delete oldest record

Author

Topic

ray_nl
Starting Member

3 Posts

Posted - 2002-10-17 : 08:20:22

Hi all,

I'm an SQL newbie and I really need some help.
I've got a History table in which old records are stored. In order to keep the database a bit small a decision was made to only store the last three mutations.
The database I've created has got an ID (which is copied from the sourcefiles ID) and a timestamp. What I want to do is the following:
At the end of the program I want to run a query which searches for records whose ID's exist more than three times and then delete the oldest one.
One problem though: I don't know how to do it!
Can anyone help me?

Thanks a lot in advance, Raymond

ray_nl
Starting Member

3 Posts

Posted - 2002-10-17 : 09:04:46

What I've got so far:

Select DISTINCTROW COUNT(History.AutoID) AS Aantal, History.AutoID
From History
Group by History.AutoID
Having (((Count(History.AutoID))>3));

If I execute this I see records which have more than 3 records for 1 autoID.
How do I get to delete them now?

Thanks, Raymond

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-10-17 : 09:23:41

What is your timestamp field ?
Access97 doesn't have a timestamp datatype.
Could it be........

> A linked SQL Server table with a timestamp datatype field ?

> Access date/time field

> Other

===========
Paul

ray_nl
Starting Member

3 Posts

Posted - 2002-10-17 : 09:31:20

It's an access long time field so it looks like this:
10/17/02 1:52:13 PM

Raymond

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-18 : 18:26:17

I used PAGE47's solution to topic:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20756

and modified it to solve your problem
Now all you need to do is wrap a delete around this select:

SELECT AutoID, DateStamp
FROM History H
WHERE EXISTS (
SELECT 1
FROM History
WHERE AutoID = H.id and DateStamp = H.DateStamp
GROUP BY AutoID
HAVING (
SELECT COUNT(*)
FROM History
WHERE AutoID = H.AutoID And DateStamp >= H.DateStamp
) > 3
)
ORDER BY AutoID

Thanks Page47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources