How to sort tables? WAS: Deleting duplicate records. 
Author Message
 How to sort tables? WAS: Deleting duplicate records.

I have given up the Access queries, and decided to use Visual Basic 4.  I
have a program that opens a database table, and compares each record.  
When it finds a dup, it deletes it.  Problem is, I found out that the
table is not sorted by the field I need it to be sorted by, so it does
not find all of the duplicates.  How do I sort this table?

I tried the
SELECT * FROM cred_chk_copy ORDERED BY FIRST_NAME , but it doesn't work.
Also, cred_chk_copy.Index = FIRST_NAME did not work either, yet
FIRST_NAME is set as an index of the table.  Before you all think I'm
nutso for not wanting duplicate first names in my table, rest assured,
this is just a test...I need to come up with a sure fire program to
delete duplicates before I unleash it on my real database.  Since it is
essentially guaranteed that there will be TONS of firt name duplication,
I chose it as my test field.  There are 1201 duplicates, and the program
only got 315 of them.



Tue, 27 Oct 1998 03:00:00 GMT  
 How to sort tables? WAS: Deleting duplicate records.

On 10 May 1996 19:47:25 GMT, Patti Johnson

[snip]

Quote:
>I tried the
>SELECT * FROM cred_chk_copy ORDERED BY FIRST_NAME , but it doesn't work.

[snip]

I think the syntax of your query shoud be:

SELECT * FROM cred_chk_copy ORDER BY FIRST_NAME;

:-)

+----------------------------------------------------------+
| Tom Childers - Arlington, Texas                          |

+----------------------------------------------------------+



Wed, 28 Oct 1998 03:00:00 GMT  
 How to sort tables? WAS: Deleting duplicate records.

Quote:

>I have given up the Access queries, and decided to use Visual Basic 4.  I
>have a program that opens a database table, and compares each record.  
>When it finds a dup, it deletes it.  Problem is, I found out that the
>table is not sorted by the field I need it to be sorted by, so it does
>not find all of the duplicates.  How do I sort this table?

Before you give up on Access, try this:

Run the "Find Duplicates" query wizard in Access. Save the
resulting query. Create a delete query based on the result of the
Find Duplicates query. Run it.

Takes about two minutes and will do what you want. If you need
this functionality in your VB program, take a look at the SQL
generated by the Find Duplicates wizard. It's based on a
self-join and works great.

FYI, the best way to handle this is to prevent the duplicate
records from getting in there in the first place.

Regards,

-----

Joe

Never underestimate the power of a WAG.



Thu, 29 Oct 1998 03:00:00 GMT  
 How to sort tables? WAS: Deleting duplicate records.

Quote:

>I have given up the Access queries, and decided to use Visual Basic 4.  I
>have a program that opens a database table, and compares each record.  
>When it finds a dup, it deletes it.  Problem is, I found out that the
>table is not sorted by the field I need it to be sorted by, so it does
>not find all of the duplicates.  How do I sort this table?
>I tried the
>SELECT * FROM cred_chk_copy ORDERED BY FIRST_NAME , but it doesn't work.
>Also, cred_chk_copy.Index = FIRST_NAME did not work either, yet
>FIRST_NAME is set as an index of the table.  Before you all think I'm
>nutso for not wanting duplicate first names in my table, rest assured,
>this is just a test...I need to come up with a sure fire program to
>delete duplicates before I unleash it on my real database.  Since it is
>essentially guaranteed that there will be TONS of firt name duplication,
>I chose it as my test field.  There are 1201 duplicates, and the program
>only got 315 of them.

Why not do:

Select DistinctRow * From ... Order by ...

And see the result.  If it still is returning too many rows to pass
your test, the odds are that you are looking a different problem
with your data.  A problem that may require data validation at
time of entry.


http://www.xnet.com/~kd9fb



Thu, 29 Oct 1998 03:00:00 GMT  
 How to sort tables? WAS: Deleting duplicate records.

Quote:


> >I have given up the Access queries, and decided to use Visual Basic 4.  I
> >have a program that opens a database table, and compares each record.
> >When it finds a dup, it deletes it.  Problem is, I found out that the
> >table is not sorted by the field I need it to be sorted by, so it does
> >not find all of the duplicates.  How do I sort this table?

> >I tried the
> >SELECT * FROM cred_chk_copy ORDERED BY FIRST_NAME , but it doesn't work.
> >Also, cred_chk_copy.Index = FIRST_NAME did not work either, yet
> >FIRST_NAME is set as an index of the table.  Before you all think I'm
> >nutso for not wanting duplicate first names in my table, rest assured,
> >this is just a test...I need to come up with a sure fire program to
> >delete duplicates before I unleash it on my real database.  Since it is
> >essentially guaranteed that there will be TONS of firt name duplication,
> >I chose it as my test field.  There are 1201 duplicates, and the program
> >only got 315 of them.

> Why not do:

> Select DistinctRow * From ... Order by ...

> And see the result.  If it still is returning too many rows to pass
> your test, the odds are that you are looking a different problem
> with your data.  A problem that may require data validation at
> time of entry.


> http://www.xnet.com/~kd9fb

There're is another way. You can use MS-Access 7 (95) query wizard. It as
an option to create a sql query to find duplicate. It works well.


Tue, 10 Nov 1998 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. I am getting duplicate records, I mean everything is duplicate even Access record number

2. Deleting Duplicate Records in a Table?

3. How do you delete duplicate records from Access table

4. How to Delete Duplicate records in Access Table ??

5. Deleting duplicate records from table

6. How to Delete Duplicate records in Access Table ??

7. Cannot add record to Visual Foxpro table after deleting all records in table

8. Sort in order to delete duplicate messages

9. Deleted records when text file is linked as table (#deleted)

10. deleting duplicate records

11. Delete duplicate records ...revisited

12. easy way to delete duplicated records?

 

 
Powered by phpBB® Forum Software