Hi Junker,
I have done this kind of thing but the way I do it is not as simple as just writing a query.
I use a Function to do processing like what you need.
I'll give you a Code outline of how I think I might approach it.
First - a simple, non-code, outline:
1. Create local variables to hold data from each of the fields you are searching for.
2. Create a Recordset object [this will read one line of Table1 at a time].
3. Set up a Do while Loop to go through each row of your Table1 data.
3a. You might need to set up an inner Loop to handle each row of data that you will be writing.
This inner loop will start when you find: Field1 = type and Field2 = name
and end when you find: Field1 = type and Field2 = name AGAIN.
When you DO find Field1 = type and Field2 = name AGAIN: you will write a row of data from your stored variables into Table2.
4. Get the different fields from the row into your variables.
5. Test your variables to see if they are Field1 = type and Field2 = name . . . etc.
6. If the variables meet your criteria - hold on to them.
7. Loop through rows in your recordset till you find Field1 = type and Field2 = name AGAIN.
8. Write a row of data to Table2.
Here's a rough outline of code that you can use.
This is not tested code just a starting point.
You'll have to play with the logic to get it right.
Code:
Function GetData()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strtemp1, strtemp2 As String
Dim str1, str2, str3, str4, str5, strSQL As String
'Integer variable to tell you if the row to be written is complete.
'This will be 2 when you hit the second Field1 = type and Field2 = name.
Dim RowComplete as Integer
RowComplete = 0
On Error GoTo Err_Routine
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
strSQL = "Select * From [Table1]"
With rs
Do While Not rs.EOF
'Get Field1 & Field2 data into your variables:
strtemp1 = rs![Field1]
strtemp2 = rs![Field2]
'Check Field1 & Field2 values.
If strtemp1 = "type" Then
If strtemp2 = "name" Then
RowComplete = RowComplete + 1
If RowComplete = 2 Then 'The row is ready to be written.
'Use something like this to write your data to Table2:
StrSQL = "INSERT INTO Table2 (Field1, Field2, Field3, Field4, Field5) "
StrSQL = StrSQL & "VALUES (" & "'" & str1 & "'" & ", " & "'" & str2& "'" & ", " & "'" & str3 & "'" & ", " & "'" & str4 & "'" & ", " & "'" & str5 & "'" & ", " "); "
'MsgBox StrSQL 'Use to test your SQL Statement.
DoCmd.RunSQL StrSQL 'Insert the Data into Table2.
'Reset all variables to 0 or "" for next
RowComplete = 0
Str1 = ""
Str2 = ""
Str3 = ""
Str4 = ""
Str5 = ""
Else 'Row is NOT ready to be written.
'Get first 3 fields for Table2 into variables.
str1 = strtemp1
str2 = strtemp2
str3 = rs![Field6]
Else If strtemp2 = "region" Then
str4 = rs![Field6]
Else If strtemp2 = "city" Then
str5 = rs![Field6]
End If
End If
End If
Loop
End With
Err_Routine:
Exit Function
End Function
I hope this helps to get you started.
I'm sorry I do not have time to get it all tested etc - but I'll help if you get stuck.
You will have to iron out the logic in my 'building blocks'.
Let me know if you run into trouble. You can start out by stripping the If Then Else part out - to test you can get your variables in and insert them.
Make a copy of Table1 with just a few rows of data [15 - 20] to test first.
All the best!
Windows XP 
Access 2007
Reply With Quote
Originally Posted by Robeen
