Delete Duplicates and Leave Firts Record Intact 2

[Stangleboy]

I was given an Excel document the shows reservation (client ID numbers) and the number of days stayed (18000+ records). So I have a lot of duplicate customer ID numbers with different number of days stayed and I want to keep the first entry and disregard the rest because the first ID number is accurate. So I export these fields to Access with a auto-primary key. So I want to delete all duplicates and my fields in my table are ID (Key)/ Customer ID/ # of days stayed. Example:
Key Cusomer ID Days stayed
1 05061234 3
2 05061234 1
3 05061234 7

I want the first record of "3" and have something delete the rest of the records (coding, macro, query, etc.). Any ideas, and thank you in advance for your time.

 

[PHV]

Simply create a table with CustomerID (PrimaryKey) and DaysStayed and then do an append query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Stangleboy]

I tried this and I get the error that primary can not have duplicates, since the Customer ID has several duplicates it will not make it the primary key. Example:

Key Cusomer ID Days stayed
1 05061234 3
2 05061234 1
3 05061234 7
4 667563215 8
5 667563215 2

The Customer IDs are in there multiple times and I just need the first entry, so for 05061234 I would just like to have the "3" and for 667563215 I would just like the "8". Thank you for your help.

 

[ItIsHardToProgram]

Create a new table is what PHV meant, in that table have customer ID be primary key and append them...

 

[Stangleboy]

Oh and just accept the prompts that come up. That works thank you both for your time.

 

[ItIsHardToProgram]

You can always remove the prompt with the Setwarnings method.

 

[ItIsHardToProgram]

DoCmd.SetWarnings False

hit submit too fast