Cleaning up duplicates based on record above in a table 1

[jdhamot]

Hi Everyone,

I posted this a few weeks ago in the Access Queries and Jet SQL Forum, but I feel that it may be better suited here. Unfortunately, due to multiple obligations with this data conversion, I had to take some time off from this part of the data. It has since become a priority again. Any help would be greatly appreciated, I have had some great experiences with the Tek-Tips community!!

I am currently dealing with a table in Microsoft Access 2003 . This is for a data conversion where my current database has a repeating Style for every barcode associated with it. The new system's table structure wants one row for each Style and the barcodes filter through multiple columns in the same row.

Below is a very simple example:

I have a table called tblBarcodes that looks like this:

Style Barcode
Shoe 100
Shoe 200
Shirt 300
Pant 400
Pant 500
Pant 600
Sweater 700

I need it to look like this:

Style Barcode1 Barcode2 Barcode3
Shoe 100 200
Shirt 300
Pant 400 500 600
Sweater 700

A colleague and I have created the VB code to do this in Excel. The problem is that this table has over 250,000 records in it and I would have to break it up multiple times in order to run it in Excel. I know it can be done in Access. I think it is something as simple as capturing the data in the line above it and comparing that to the current line of my recordset. Sounds easy when typing it obviously.

Thank you!
Josh

 

[HarleyQuinn]

I think it would be worth looking at this FAQ, faq701-3499

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

The new system's table structure wants one row for each Style and the barcodes filter through multiple columns in the same row.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I agree with PHV this is a non-normalized design, not sure why you would want to structure a table this way. If you can avoid making your table this way avoid it. That said if you really had to

Public Sub makeBarCodes()
  Dim rsOldBarCodes As DAO.Recordset
  Dim rsNewBarCodes As DAO.Recordset
  Dim oldQueryName As String
  Dim oldStyleName As String
  Dim oldBarCodeName As String
  Dim newTableName As String
  Dim newStyleName As String
  Dim intFieldStartIndex As Integer
  Dim intFieldCount As Integer
  Dim tempStyle As String
  
  oldQueryName = "tblBarCode"
  newTableName = "tblBarCode2"
  oldStyleName = "style"
  newStyleName = "style"
  oldBarCodeName = "barCode"
  intFieldStartIndex = 2
  'fields are indexed starting at 0, so the index should be the number - 1 of where the
  'bar codes start, in my case ID,style,barcode1,barcode2 therefore 2 represents
  'the index of the first (barcode1) field
  
  Set rsOldBarCodes = CurrentDb.OpenRecordset(oldQueryName, dbOpenDynaset)
  Set rsNewBarCodes = CurrentDb.OpenRecordset(newTableName, dbOpenDynaset)
  
  Do While Not rsOldBarCodes.EOF
    If Not tempStyle = rsOldBarCodes.Fields(oldStyleName) Then
       intFieldCount = intFieldStartIndex
       rsNewBarCodes.AddNew
       rsNewBarCodes.Fields(newStyleName) = rsOldBarCodes.Fields(oldStyleName)
       rsNewBarCodes.Fields(intFieldCount) = rsOldBarCodes.Fields(oldBarCodeName)
       tempStyle = rsOldBarCodes.Fields(oldStyleName)
       rsNewBarCodes.Update
    Else
       intFieldCount = intFieldCount + 1
       rsNewBarCodes.MoveLast
       rsNewBarCodes.Edit
       rsNewBarCodes.Fields(intFieldCount) = rsOldBarCodes.Fields(oldBarCodeName)
       rsNewBarCodes.Update
    End If
    rsOldBarCodes.MoveNext
  Loop
End Sub

I used variables so you could insert your field and table names. I kept this simple make making my new table and putting in enough barcode fields. You can easily do this by finding which style has the most barcodes. It would be more difficult to dynamically add bar code fields.

Did I mention that this is a poor table design?

 

[MajP]

This assumed that the old data is based on a sorted query with no duplicate records. Like you show

oldQueryName: name of query where old data is coming from

oldStyleName: name of the style field in old table
oldBarCodeName: name of the bar code field in old table
newTableName: name of the new table
newStyleName: name of the new style field in new table

 

[jdhamot]

Thank you everyone for your replies. Sorry I did not respond sooner, but I have been swamped all weekend and this morning. I will look over everyone's suggestions and update you all on my progress.

I am under the impression that this is how the flat file needs to be formatted in order for the import software to work. I have to get our data out of the old system to look exactly how they have their import software set up for the new system. I have no control over how our old system's table structure for storing the data was or how the new system's table structure stores the data.

Thank you all again for your replies it is much appreciated. I hope to be able to look at this tonight.

Josh

 

[jdhamot]

MajP,

Thank you very much for that portion of code. I had to make a few changes since my table structure and issue wasnt quite as simple as my example, but by using your code as a foundation I was able to achieve my goal.

Once again I had a great experience in this helpful community and would like to thank everyone else that participated in this thread.

Have a great day!
Josh