I would like to know if this is possible.
I have a data table arranged this way:
PRNo Stage1 stage2 stage3 stage4.....stage9
1 3 0 0 0 ….. 0
I would like to possibly run a query (or something) so that the table above becomes:
PRNo Stage Status
1 1 3
1 2 0
1 3 0
1 4 0
and so on...
I have used below code but it is for 2 field only, any help please?
also if possible when run the code every time, i need to update the existing record not to duplicate it, is it possible?
Thanks in advance.
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, s, fldArr()
Set rs = CurrentDb.OpenRecordset("StagesQ") 'change the name to the actual name of table
Set rs1 = CurrentDb.OpenRecordset("Invoices")
If rs.EOF Or rs.BOF Then
MsgBox "no records"
For i = 0 To rs.Fields.Count - 1
ReDim Preserve fldArr(i)
fldArr(i) = rs.Fields(i).Name
Do Until rs.EOF
For j = 1 To UBound(fldArr)
!PRNo = rs("PRNo")
!Discerption = rs.Fields(fldArr(j))