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

[nischay04]

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

[NeoPa]

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.

[nischay04]

Thanks Neo, I have updated the query. Hope this make sense

[NeoPa]

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.

[cactusdata]

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:

Public Function ExportContacts()

    Dim rs  As DAO.Recordset
    Dim Id  As String
    
    Set rs = CurrentDb.OpenRecordset("Select * From Contacts Where Exported = False Order By 1, 2")
    
    While Not rs.EOF
        If Id <> rs!CustomerID.Value Then
            Id = rs!CustomerID.Value
            ' Run export - like appending data to another table.
            Debug.Print rs!CustomerID.Value, rs!ContactID.Value, rs![Contact number].Value
            rs.Edit
                rs!Exported.Value = True
            rs.Update
        End If
        rs.MoveNext
    Wend
    rs.Close
    
End Function

Results from 1st, 2nd, 3rd run of the above function:

10011         123XXY        04XXXXXX
10012         113XXY        02XXXXXX

10011         223XXY        03XXXXXX
10012         133XXY        07XXXXXX

10011         423XXY        08XXXXXX