How to Group records based on field row value

[lexi0088]

I have a table in my database (which is a text file import from raw edi data) that I need to somehow group. I would like to first loop through the table and assign a group name to the records. Here is the example of what the table data might look like:

Field1 Field2 Field3 Field4
PTD SS
N1 ST BLOCKED PER CUSTOMER 92
N4 211
QTY 32 1 EA
LIN N4 00525041030
UIT EA 39.74 WH
REF IN 508456
REF QK 1
DTM 003 20080820
QTY 32 1 EA
LIN N4 00525801990
UIT EA 73.35 WH
REF IN 611225
REF QK 1
DTM 003 20080829
QTY 32 1 EA
LIN N4 00525041030
UIT EA 39.74 WH
REF IN 785217
REF QK 1
DTM 003 20080822
PTD SS
N1 ST BLOCKED PER CUSTOMER 92
N4 200
QTY 32 1 EA
LIN N4 00525090690
UIT EA 61.25 WH
REF IN 983383
REF QK 1
REF CT 5330
DTM 003 20080825


This is very difficult for me since every "group" does not have a consistency in how many rows it contains. Each "group" of rows begins where Field1 = "PTD". What I would like to do, is add a Field5 that will identify each group, where every row under the first PTD has a Field5= GRP1. Then every row under the second PTD has a Fild5 = GRP2.

I know there must be some way of doing this in VBA, any help would be appreciated!!

Thank you,

 

[Remou]

If you are looping through the table, why not normalize it by making each "group" into a row in a table? This would be a more usual approach.

 

[Remou]

... I hope you included an autonumber, otherwise you will shortly have no order to the data at all. Access does not have an 'import' order, unless you create one.

 

[lexi0088]

Remou,

I have thought about the autonumber situation, and that will be a step in my import. As far as making each group a row in the table... that is exactly what I am trying to do. This table is an import of a text file. My first step is to identify each group (hence the loop I am trying to create) then transform the data where each group will be in one row.

I assume that I need to have a loop with in a loop to accomplish this grouping I am trying to do. I just cannot figure out how to start and stop a loop based on the value of the field, rather than the number of the row.

 

[Remou]

Ok. Here are some notes.

Set db=CurrentDB
'Build table to hold data
strSQL="SELECT DISTINCT Field1 FROM tblT"

Set rs=db.OpenRecordset(strSQL)

Do While Not rs.EOF
  strSQL=strSQL & ",[" & rs!Field1 & "] Text(255)"
  rs.MoveNext
Loop

strSQL="MAKE TABLE NewTable (" & Mid(strSQL,2) & " )"
db.Execute strSQL

'Fill the table

Set rs=db.OpenRecordset("SELECT * FROM tblT ORDER BY ID")
Set rsOut=db.OpenRecordset("SELECT * FROM NewTable")
Do While Not rs.EOF
   If rs.Field1="PSS" Then
      rsOut.Update
      rsOut.AddNew
   End If

   rsOut.Fields(rs!Field1)=Replace(rs!Field2 & " " _
      & rs!Field3 & " " & rs!Field4, "'","''")

   rs.MoveNext
Loop

Or something on those lines