I have a table with the following values.
Groep_Mach / orderID / Stap / ART
M01 / 1 / 10 / art1
M05 / 1 / 20 / art1
M03 / 1 / 30 / art1
M02 / 2 / 10 / art1
M05 / 2 / 20 / art1
M10 / 2 / 30 / art1
In a new table I want to see it as follows
orderID / ART / flow
1 / art1 / M01-M05-M03
2 / art1 / M02-M05-M10
I do it in a query with VBA
the query is:
SELECT ORDERID, Stap, ART, SamenSort([ORDERID]) AS Field
FROM tblGroepMachineDummy
ORDER BY ORDERID, Stap;
VBA is
Function SamenSort(Pnr As String) As String
Dim rs As DAO.Recordset
Dim Ini As Boolean
Dim SQL As String
Ini = True
SQL = "SELECT ORDERID, Stap, Groep_Machines " _
& "FROM tblGroepMachineDummy " _
& "WHERE (((ORDERID) = """ & Pnr & """))" _
& "ORDER BY ORDERID, Stap;"
Set rs = CurrentDb.OpenRecordset(SQL)
Do While Not rs.EOF
If Ini Then
SamenSort = rs!Groep_Machines
Ini = False
Else
SamenSort = SamenSort & "_" & rs!Groep_Machines
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function
The query is working, but my problem is, if i want to make a 'Create table' query the query hangs. any solutions? or a better way to do it? more than 300000 records