SEPARATING DIFFERENT DATA TYPES STACKED IN THE SAME FIELD

[JLC]

I IMPORT FROM A SALES 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 # 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. FOR OUR LAST SHOW,
THE TOTAL NUMBER OF RECORDS WAS 2,486.

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

 

[Smartin]

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

Hi JLC,

Firstly do please disengage your caps lock in future posts.

Secondly I'm guessing this sort of problem has been approached before,
since it embodies decomposing sequential files into a normalized data
structure. However I'm not at all familiar with specific solutions so
this is how I would go about it.

A VBA routine that imports the data row by row can detect a change on
TRANS_TYPE and accordingly, store the corresponding value in the
appropriate table. You seem to have two tables, TRNS and SPL, where the
latter is a parent to the former. This suggests a one-to-many
relationship between these objects.

So the idea is to loop through the input and store child SPL values
until the TRNS value changes or the EOR marker (ENDTRNS) is reached.
Along the way you will make entries to both TRNS and SPL tables. This
takes a bit of coding skill, but nothing heroic.

Hope this gives you some ideas.

 

[Gary Walter]

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

Pardon me for jumping in...

I'm not sure what "number" you are after,
but I believe following will put sales values
with their associated transaction number into a
table "tblSales" (change "tblJLC" to actual
name of your table).

SELECT
t1.ID,
t1.[TRANS TYPE],
Abs(Val([t1].[NUM-AMT])) AS Sale,
t2.[NUM-AMT] AS TRNSNum
INTO tblSales
FROM tblJLC AS t1, tblJLC AS t2
WHERE
(((t1.[TRANS TYPE])="SPL")
AND
((t2.ID)=
(SELECT MAX(q.ID)
FROM tblJLC As q
WHERE
q.[TRANS TYPE]="TRNS" AND q.ID<=t1.ID)));

 

[JLC]

Gary,

The problem originates in the PALM-based order scanner we use at tradeshows.
I am trying to get the sales order # for each SPL = item sold.

I tried your suggestion, but Access prompted for parameter values for
q.TRANS TYPE (I entered “TRNSâ€) and t.TRANS TYPE (I entered “SPLâ€). The new
table kept the same stacking, even in the new TRNSNum field. Any further
suggestions?

JERRY COLBURN

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

Pardon me for jumping in...

I'm not sure what "number" you are after,
but I believe following will put sales values
with their associated transaction number into a
table "tblSales" (change "tblJLC" to actual
name of your table).

SELECT
t1.ID,
t1.[TRANS TYPE],
Abs(Val([t1].[NUM-AMT])) AS Sale,
t2.[NUM-AMT] AS TRNSNum
INTO tblSales
FROM tblJLC AS t1, tblJLC AS t2
WHERE
(((t1.[TRANS TYPE])="SPL")
AND
((t2.ID)=
(SELECT MAX(q.ID)
FROM tblJLC As q
WHERE
q.[TRANS TYPE]="TRNS" AND q.ID<=t1.ID)));

 

[Gary Walter]

What is the real name of the field if it is not [TRANS TYPE]?

Replace in query.

The problem originates in the PALM-based order scanner we use at
tradeshows.
I am trying to get the sales order # for each SPL = item sold.

I tried your suggestion, but Access prompted for parameter values for
q.TRANS TYPE (I entered "TRNS") and t.TRANS TYPE (I entered "SPL"). The
new
table kept the same stacking, even in the new TRNSNum field. Any further
suggestions?

JERRY COLBURN

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

Pardon me for jumping in...

I'm not sure what "number" you are after,
but I believe following will put sales values
with their associated transaction number into a
table "tblSales" (change "tblJLC" to actual
name of your table).

SELECT
t1.ID,
t1.[TRANS TYPE],
Abs(Val([t1].[NUM-AMT])) AS Sale,
t2.[NUM-AMT] AS TRNSNum
INTO tblSales
FROM tblJLC AS t1, tblJLC AS t2
WHERE
(((t1.[TRANS TYPE])="SPL")
AND
((t2.ID)=
(SELECT MAX(q.ID)
FROM tblJLC As q
WHERE
q.[TRANS TYPE]="TRNS" AND q.ID<=t1.ID)));

 

[JLC]

Thanks Gary,

I negligently wrote "TRANS TYPE" instead of the correct "TRNS TYPE." After
correcting my own error, I ran the query you had suggested, and it produced
the right table.

Thanks for the learning.

Jerry Colburn

What is the real name of the field if it is not [TRANS TYPE]?

Replace in query.

The problem originates in the PALM-based order scanner we use at
tradeshows.
I am trying to get the sales order # for each SPL = item sold.

I tried your suggestion, but Access prompted for parameter values for
q.TRANS TYPE (I entered "TRNS") and t.TRANS TYPE (I entered "SPL"). The
new
table kept the same stacking, even in the new TRNSNum field. Any further
suggestions?

JERRY COLBURN

:

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



Pardon me for jumping in...

I'm not sure what "number" you are after,
but I believe following will put sales values
with their associated transaction number into a
table "tblSales" (change "tblJLC" to actual
name of your table).

SELECT
t1.ID,
t1.[TRANS TYPE],
Abs(Val([t1].[NUM-AMT])) AS Sale,
t2.[NUM-AMT] AS TRNSNum
INTO tblSales
FROM tblJLC AS t1, tblJLC AS t2
WHERE
(((t1.[TRANS TYPE])="SPL")
AND
((t2.ID)=
(SELECT MAX(q.ID)
FROM tblJLC As q
WHERE
q.[TRANS TYPE]="TRNS" AND q.ID<=t1.ID)));