Access data - crosstab?? - Now Resolved

[Mr_Floyd1976]

Hi,

I have a table of data imported from our company's main system.

The data shows duplicate records that have been assigned new codes.

i.e.

Code Surname FirstName Title DOB
1001 Smith Bob Mr 02/02/1976
1234 Smith Bob Mr 02/02/1976
.................
...............

etc

There are a few thousand of these.

What I want is the data in the format

Code1 Code2 Surname FirstName Title DOB
1001 1234 Smith Bob Mr 02/02/1976

Any ideas? I am really struggling at the moment

Cheers

Mark

[westconn1]

is 2 the maximum number of times they can be duplicated? or could bob smith have ½ dozen records.
i assume you want to put the data in a new table, but this is unclear to me, or do you just want to put them in a report or printout

pete

[Mr_Floyd1976]

yes 2 is the maximum, well the most I have found. Could possibly be more.

Yes, i need the data in a new table

[westconn1]

does the table already exist or does it need to be created?

what code are you using now to access the data?

[Mr_Floyd1976]

does the table already exist or does it need to be created?

what code are you using now to access the data?

Doesn't really matter.. I just want to be able to get the data in the format I require..

The table is currently sitting in Access and has been extracted using Monarch.

[westconn1]

Dim rstplant As Recordset, rstplant2 As Recordset, rstdupes As Recordset
    Set rstplant = dbsplant.OpenRecordset("plant_details", dbOpenDynaset)
    Set rstplant2 = dbsplant.OpenRecordset("plant_details", dbOpenDynaset)
    Set rstdupes = dbsplant.OpenRecordset("duplicates", dbOpenDynaset)
For i = 0 To rstplant.RecordCount - 1
    
    For j = i - 1 To rstplant2.RecordCount - 1
        If rstplant(2) = rstplant2(2) And rstplant(3) = rstplant2(3) And rstplant(5) = rstplant2(5) Then
              rstdupes.AddNew
                rstdupes(1) = rstplant(1)
                rstdupes(2) = rtsplant2(1)
                rstdupes(3) = rstplant(2)
                rstdupes(4) = rstplant(3)
                rstdupes(5) = rstplant(4)
                rstdupes(6) = rstplant(5)
            rstdupes.Update
            rstplant.MoveFirst
            Exit For
        End If
    
        
        rstplant2.MoveNext
    Next
    rstplant.MoveNext
Next

this is code to do what you ask, using 2recordsets from your table and a second table of dupicates to add to, you will have to change this to reference your tables.

pete