Need help building query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Adrienne Boswell - 15 Jun 2007 07:32 GMT

Using Access and ASP (going to migrate to something more robust when this
is cleaned up):

Given the following information (I know that one card has been assigned
three times)
ID Card Number      M_id Added
602    8888177601005601    2    2005-04-11 00:00:00
5101    8888177601005601    11    2006-05-31 00:00:00
5601    8888177601005601    11    2006-05-31 00:00:00

I can run this query and get a list of card numbers that have been
assigned more than once:
SELECT cardnumber, COUNT(cardnumber)
FROM cards
GROUP BY cardnumber
HAVING COUNT(cardnumber) > 1

What I need is a query that will delete the first two records and leave
only one record (the last one entered) remaining. I know I can do this
in ASP (create a new table with a unique index, put the records in it in
reverse order, delete all from the current and put them back in reverse
order again), but I would like to know it there is a query to do it.

Any help or ideas greatly appreciated. TIA.

FYI: When I originally had this account, the table had a unique index on
cardnumber. The client and I had a falling out, and he must have had
problems trying to do duplicate entries, so the new person removed the
index - ergo over 5000 duplicate cards. Now I have the account back, and
the dubious honor of cleaning up the mess.
<Rant>
Client: I can't shoot myself in the foot.
Me: The safety is on.
Client: I can't shoot myself in the foot so I am going to get someone
else.
Me: OK.
Client: I can't shoot myself in the foot.
New AE: The safety has been removed.
Client: I shot myself in the foot. Why does my foot hurt?
</Rant>

Signature

Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Reply to this Message

Bob Barrows [MVP] - 15 Jun 2007 14:43 GMT

> Using Access and ASP (going to migrate to something more robust when
> this is cleaned up):
[quoted text clipped - 21 lines]
>
> Any help or ideas greatly appreciated. TIA.

Well, this query will return the last entry date for each card:

SELECT cardnumber, max(Added) LastEntryDate
FROM cards
GROUP BY cardnumber

While you could do a series of subqueries, for readability and
maintainability, I would suggest creating a saved query called
LatestEntryDatePerCard using that sql and use it in this query that
retrieves the ID of the last entry for each card (again, creating a saved
query - call it LatestCardIDs):

Select c.ID
From cards c join LatestEntryDatePerCard q
ON c.cardnumber =q.cardnumber and c.Added = q.LastEntryDate

Now this query should delete the earlier entries:

delete c.* from
cards c left join LatestCardIDs q
on c.ID = q.ID
WHERE q.ID Is Null

Probably more efficient would be to do this:

Select c.* into UniqueCards from
cards c join LatestCardIDs q
on c.ID = q.ID

Delete * From cards

Create the unique index on cards, and then:

insert into cards
select * from UniqueCards

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Adrienne Boswell - 15 Jun 2007 15:36 GMT

>> Using Access and ASP (going to migrate to something more robust when
>> this is cleaned up):
[quoted text clipped - 57 lines]
> insert into cards
> select * from UniqueCards

Thanks, Bob - you're always my hero! And Happy Father's Day!

Signature

Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Reply to this Message

Adrienne Boswell - 17 Jun 2007 02:40 GMT

> Create the unique index on cards, and then:
>
> insert into cards
> select * from UniqueCards

That's what I ended up doing in the end. Believe me, I had the client here
while I was doing this, and he thought of at least four other more
complicated ways of doing it.

Signature

Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Reply to this Message

Webmaster Forum / ASP / Database Access / June 2007

Need help building query