By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Changing a duplicate select query into a delete query, help!!

P: n/a
JC
Hello,

I am trying to change a select, find duplicates, query into a delete
query. I want to get rid of the records in the main table
"tblHurnsHistory." I changed the Find Duplicates query to a Delete
query. I then received an input box looking for criteria that
states,"

Enter parameter value
qryHurnsHistory.Grads_creditsEarned

Then it deletes 0 records. I need it to delete the 48 that the Find
Duplicates query returned.

I am a little rusty at SQL, can you help me so the following SQL
deletes the records returned by the FindDuplicates query.
DELETE HHDelete.SyStudentID, HHDelete.Grads_creditsEarned, HHDelete.ID,
HHDelete.StudentName, HHDelete.SSN, HHDelete.adTrmCode,
HHDelete.GPA_creditsEarned, HHDelete.StatusDesc, HHDelete.StatusDate,
HHDelete.LDA, HHDelete.ProgVersCode, HHDelete.TermCode,
HHDelete.term_credits, HHDelete.TotalCrd, HHDelete.creditsAttempt,
HHDelete.GPA, HHDelete.CreditsRemaining, HHDelete.SumOfReceived,
HHDelete.SumOfRefunded, HHDelete.FundSource
FROM tblHurnsHistory AS HHDelete
WHERE (((HHDelete.SyStudentID) In (SELECT [SyStudentID] FROM
[qryHurnsHistory] As Tmp GROUP BY [SyStudentID],[Grads_creditsEarned]
HAVING Count(*)>1 And [Grads_creditsEarned] =
[qryHurnsHistory].[Grads_creditsEarned])));
Thanks in advance,

Justin

Oct 20 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I suspect you need to rewrite your query eliminating all group by
clauses, including any in qryHurnsHistory. Alas you do not state your
table's primary key. In the following example, the primary key is
my_table.my_id1. My_table.my_count and my_table.my_id2 are the
duplicated fields.

DELETE my_table.*
FROM my_table
WHERE ((((select count(*) from my_table b where b.my_id2 =
my_table.my_id2 and b.my_count = my_table.my_count and my_table.my_id >
b.my_id))>0));

Above uses a subquery and is probably the style of query that you need.
Try help on 'subquery' and 'table alias' if you are new to the
technique.

Good Luck.
Breadon

Oct 23 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.