SEPARATING DIFFERENT DATA TYPES STACKED IN THE SAME FIELD

[JLC]

I IMPORT FROM A TRANSACTIONAL DATA PROGRAM THAT STACKS SALES ORDER# AND SALES
PRICE IN THE SAME FIELD.

I USE AN AUTO ID WHEN I CREATE THE TABLE.

THE IMPORT LOOKS LIKE THIS:

ID TRANS TYPE NUM-AMT
1 TRNS 50001
2 SPL -79.95
3 SPL -199.95
4 ENDTRNS

WITH EACH SALES ORDER, THE FIRST IMPORTED FIELD “TRNS TYPE†SETS THE
BOUNDARIES. THE FIRST RECORD IS “TRNS,†THE ITEMS ORDERED ARE “SPL,†AND
“ENDTRNS†MARKS THE END OF THE SALES ORDER.

THE “NUM-AMT†FIELD HAS THE SALES ORDER NUMBER FOR THE TRNS RECORD. THE
ENSUING SPL RECORDS HAVE SALES PRICE, AND ENDTRNS IS BLANK.

I CREATED A NUM FIELD, BUT I CAN’T FIND WAY OF UPDATING IT FOR EACH ORDER,
USING THE SALES ORDER NUMBER FROM THE TRNS RECORD.

THE SALES ORDERS VARY IN SIZE FROM ONE ITEM TO OVER 100. THE TOTAL NUMBER OF
RECORDS IS 2486. I CAN OF COURSE DO THIS MANUALLY AND SEMI-MANUALLY IN EITHER
ACCESS OR EXCEL, BUT IF SOMEBODY OUT THERE HAS A BETTER WAY, I WOULD GREATLY
APPRECIATE LEARNING.

JLC

 

[JLC]

A SLIGHT EDIT, THE MODEL TABLE WITH TWO SALES ORDERS:

ID TRANS TYPE NUM-AMT
1 TRNS 50001
2 SPL -79.95
3 SPL -199.95
4 ENDTRNS
5 TRNS 50002
6 SPL -79.95
7 SPL -199.95
8 SPL -299.95
9 ENDTRNS