SELECT * from tblHolland;
ID |
TIME |
FROM |
MESSAGE |
COORDINATES |
ASSESSMENT |
1 |
20230214 |
JAN |
Test message 1 |
XYZ1 |
1x Watch / 2x Timepieces |
2 |
20230213 |
PETER |
Test message 2 |
XYZ2 |
1x Watch |
3 |
20230212 |
JAN |
Test |
XYZ4 |
3x Watch / 3x Timepieces |
4 |
20230211 |
HANS |
Test message |
XYZ3 |
2x Timepieces |
SELECT `ID`, TIME, `FROM`, MESSAGE, COORDINATES, Left([ASSESSMENT],1) AS Qty1, Mid(Left([ASSESSMENT],InStr([ASSESSMENT] & "/","/")-1),4) AS WorT FROM tblHolland union all SELECT `ID`, TIME, `FROM`, MESSAGE, COORDINATES, Left(Mid([ASSESSMENT],InStr([ASSESSMENT] & "/","/")+2),1), Mid([ASSESSMENT],InStr([ASSESSMENT] & "/ ","/")+5) AS WorT2 FROM tblHolland order by 1;
ID |
TIME |
FROM |
MESSAGE |
COORDINATES |
Qty1 |
WorT |
1 |
20230214 |
JAN |
Test message 1 |
XYZ1 |
2 |
Timepieces |
1 |
20230214 |
JAN |
Test message 1 |
XYZ1 |
1 |
Watch |
2 |
20230213 |
PETER |
Test message 2 |
XYZ2 |
|
|
2 |
20230213 |
PETER |
Test message 2 |
XYZ2 |
1 |
Watch |
3 |
20230212 |
JAN |
Test |
XYZ4 |
3 |
Timepieces |
3 |
20230212 |
JAN |
Test |
XYZ4 |
3 |
Watch |
4 |
20230211 |
HANS |
Test message |
XYZ3 |
|
|
4 |
20230211 |
HANS |
Test message |
XYZ3 |
2 |
Timepieces |