Hi,<br><br>I have a table where there are many records for one transaction.If I only want one record of the transaction how can I delete all but one record? I am trying to use an SQL solution like this:<br><br>WHERE (((Transactions.TransID) In (select transid from Transactions as tmp group by transid having count (*)>1)));<br><br>But how can I get this query to leave just one transaction behind?<br><br>Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Delete Duplicates 1
- Thread starter davo
- Start date
-
1
- #2
Lots of ways, here's one: Write a make-table query: sort on whatever field would make the records unique if there wer no dupes, then add a min or max expression (using the totals line) on one of the fields (doesn't matter which one) to grab just one of them. When you're done, rename your old table (say it's now named OldTable) xOldTable, then rename your new table OldTable. If you find you've made an error you haven't lost your old stuff.
- Thread starter
- #3
- Status
Similar threads
- Locked
- Question