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

How to pull list of record by pulling 1st record of duplicate IDs

P: 2
Hi All,

I am looking for guidance in MS access.

I have a data table with 3 column CustomerID, ContactID and Contact number. The CustomerID column has duplicate IDs but ContactID has unique IDs. I want to export data this table using CustomerID but only 1st instance of each duplicate and any unique CustomerID so let say I was to pull 10011 1st instance in one table.
Likewise, I would need to create second export with all 2nd instance duplicate in different table and so on.

Can I do that?

below is example data
CustomerID ContactID Contact number
10011 123XXY 04XXXXXX
10012 113XXY 02XXXXXX
10011 223XXY 03XXXXXX
10012 133XXY 07XXXXXX
10011 423XXY 08XXXXXX
6 Days Ago #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Hi Nischay.

Welcome to Bytes.com.

At this point I can only advise that you edit your question so that it makes sense to people who don't already know what you're talking about. Many won't say anything but just ignore your question because it's so unclear what you mean.

Generally speaking, if you update it of course, you'll see a number jumping in trying to help. Left as it is then not so much.

Good luck.
6 Days Ago #2

P: 2
Thanks Neo, I have updated the query. Hope this make sense
6 Days Ago #3

NeoPa
Expert Mod 15k+
P: 31,492
Not a lot if I'm honest. My best guess would be that you mean you have it working and no longer need help, but that can only be a guess at this point.
6 Days Ago #4

P: 18
You can write some fancy SQL using TOP n in several levels, but it is much simpler to add a field, Exported, to the table, then loop the table while marking the records as exported:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExportContacts()
  2.  
  3.     Dim rs  As DAO.Recordset
  4.     Dim Id  As String
  5.  
  6.     Set rs = CurrentDb.OpenRecordset("Select * From Contacts Where Exported = False Order By 1, 2")
  7.  
  8.     While Not rs.EOF
  9.         If Id <> rs!CustomerID.Value Then
  10.             Id = rs!CustomerID.Value
  11.             ' Run export - like appending data to another table.
  12.             Debug.Print rs!CustomerID.Value, rs!ContactID.Value, rs![Contact number].Value
  13.             rs.Edit
  14.                 rs!Exported.Value = True
  15.             rs.Update
  16.         End If
  17.         rs.MoveNext
  18.     Wend
  19.     rs.Close
  20.  
  21. End Function
Results from 1st, 2nd, 3rd run of the above function:

Expand|Select|Wrap|Line Numbers
  1. 10011         123XXY        04XXXXXX
  2. 10012         113XXY        02XXXXXX
  3.  
  4. 10011         223XXY        03XXXXXX
  5. 10012         133XXY        07XXXXXX
  6.  
  7. 10011         423XXY        08XXXXXX
5 Days Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.