Separate Field Content into new Records

[jcamrud via AccessMonster.com]

I have a table with a text field named “aliq†that can contain from 1 to 16
“Xâ€s. If the “X†is in the first space of the field, it stands for the
letter “A†(example: X__X is equal to A and D).

If the field “aliq†in one record contains 4 “Xâ€s (X XX X), I need to
have it separated into 4 individual records with the field “aliq†populated
with “A†for the first record, “E†for the second record, “F†for the third
record and “J†for the fourth record. The “Xâ€s will be replaced.

MS Access 2007
Table 1
Field1(text)
Field2(text)
Field3 (text)
Aliq(text)


Thanks in advance.

 

[Stefan Hoffmann]

hi,

If the field “aliq†in one record contains 4 “Xâ€s (X XX X), I need to
have it separated into 4 individual records with the field “aliq†populated
with “A†for the first record, “E†for the second record, “F†for the third
record and “J†for the fourth record. The “Xâ€s will be replaced.

You need Mid() to extract the character and build a unionn query with
it, e.g.

SELECT "A" FROM yourTable WHERE Mid([aliq], 1, "X") = "X"
UNION ALL
SELECT "E" FROM yourTable WHERE Mid([aliq], 2, "X") = "X"
UNION ALL
SELECT "F" FROM yourTable WHERE Mid([aliq], 3, "X") = "X"
UNION ALL
...



mfG
--> stefan <--

 

[jcamrud via AccessMonster.com]

Attempted to run query and received the following message:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'.

I've tried to tweak with no success. Please help.

 

[Stefan Hoffmann]

Attempted to run query and received the following message:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'.

What query? Post it.


mfG
--> stefan <--

 

[jcamrud via AccessMonster.com]

SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, "X") = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, "X") = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, "X") = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7, "X") = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, "X") = "X"
UNION ALL
SELECT "I" FROM BENNETT WHERE Mid([p_aliq], 9, "X") = "X"
UNION ALL
SELECT "J" FROM BENNETT WHERE Mid([p_aliq], 10, "X") = "X"
UNION ALL

 

[Douglas J. Steele]

Get rid of the last UNION ALL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, "X") = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, "X") = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, "X") = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7, "X") = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, "X") = "X"
UNION ALL
SELECT "I" FROM BENNETT WHERE Mid([p_aliq], 9, "X") = "X"
UNION ALL
SELECT "J" FROM BENNETT WHERE Mid([p_aliq], 10, "X") = "X"
UNION ALL

What query? Post it.

mfG
--> stefan <--

 

[John W. Vinson]

SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, "X") = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, "X") = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, "X") = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7, "X") = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, "X") = "X"
UNION ALL
SELECT "I" FROM BENNETT WHERE Mid([p_aliq], 9, "X") = "X"
UNION ALL
SELECT "J" FROM BENNETT WHERE Mid([p_aliq], 10, "X") = "X"
UNION ALL

Actually a couple of errors there. The "X" in the third argument to Mid()
should be a 1 (to extract one byte); and (if you don't already have one) you
need an A, B and on through P. As Douglas says you also need to get rid of the
hanging UNION ALL at the end:

SELECT "A" FROM BENNETT WHERE Mid([p_aliq], 1, 1) = "X"
UNION ALL
SELECT "B" FROM BENNETT WHERE Mid([p_aliq], 2, 1) = "X"
UNION ALL
SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, 1) = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, 1) = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, 1) = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, 1) = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7 ,1) = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, 1) = "X"

<etc up through>
SELECT "P" FROM BENNETT WHERE Mid([p_aliq], 16, 1) = "X";

I must say this seems like a VERY shaky and peculiar way of entering data!
Whoever's typing X X X XX X X X will need a steady hand and some good way
to count blanks, if they are indeed typing it.

 

[jcamrud via AccessMonster.com]

I now get the message: "Data type mismatch in criteria expression." The
fields are all text.
This is what I have now:
SELECT "A" FROM BENNETT WHERE Mid([p_aliq], 1, "X") = "X"
UNION ALL
SELECT "B" FROM BENNETT WHERE Mid([p_aliq], 2, "X") = "X"
UNION ALL
SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, "X") = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, "X") = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, "X") = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7, "X") = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, "X") = "X"
UNION ALL
SELECT "I" FROM BENNETT WHERE Mid([p_aliq], 9, "X") = "X"
UNION ALL
SELECT "J" FROM BENNETT WHERE Mid([p_aliq], 10, "X") = "X"
UNION ALL
SELECT "K" FROM BENNETT WHERE Mid([p_aliq], 11, "X") = "X"
UNION ALL
SELECT "L" FROM BENNETT WHERE Mid([p_aliq], 12, "X") = "X"
UNION ALL
SELECT "M" FROM BENNETT WHERE Mid([p_aliq], 13, "X") = "X"
UNION ALL
SELECT "N" FROM BENNETT WHERE Mid([p_aliq], 14, "X") = "X"
UNION ALL
SELECT "O" FROM BENNETT WHERE Mid([p_aliq], 15, "X") = "X"
UNION ALL
SELECT "P" FROM BENNETT WHERE Mid([p_aliq], 16, "X") = "X";

Get rid of the last UNION ALL

SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL

[quoted text clipped - 21 lines]

 

[jcamrud via AccessMonster.com]

Thank you both! The data is spatial so it doesn't look valid to the human
eye.

SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL

[quoted text clipped - 12 lines]

SELECT "J" FROM BENNETT WHERE Mid([p_aliq], 10, "X") = "X"
UNION ALL

Actually a couple of errors there. The "X" in the third argument to Mid()
should be a 1 (to extract one byte); and (if you don't already have one) you
need an A, B and on through P. As Douglas says you also need to get rid of the
hanging UNION ALL at the end:

SELECT "A" FROM BENNETT WHERE Mid([p_aliq], 1, 1) = "X"
UNION ALL
SELECT "B" FROM BENNETT WHERE Mid([p_aliq], 2, 1) = "X"
UNION ALL
SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, 1) = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, 1) = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, 1) = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, 1) = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7 ,1) = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, 1) = "X"

<etc up through>
SELECT "P" FROM BENNETT WHERE Mid([p_aliq], 16, 1) = "X";

I must say this seems like a VERY shaky and peculiar way of entering data!
Whoever's typing X X X XX X X X will need a steady hand and some good way
to count blanks, if they are indeed typing it.

 

[jcamrud via AccessMonster.com]

One last request. Some of the records contain "ENTIRE SECTION" in the
"p_aliq" field rather than "X". I need to have these records converted into
16 records with the field "p_aliq" populated with "A" through "P".

Thanks again!

SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL

[quoted text clipped - 12 lines]

SELECT "J" FROM BENNETT WHERE Mid([p_aliq], 10, "X") = "X"
UNION ALL

Actually a couple of errors there. The "X" in the third argument to Mid()
should be a 1 (to extract one byte); and (if you don't already have one) you
need an A, B and on through P. As Douglas says you also need to get rid of the
hanging UNION ALL at the end:

SELECT "A" FROM BENNETT WHERE Mid([p_aliq], 1, 1) = "X"
UNION ALL
SELECT "B" FROM BENNETT WHERE Mid([p_aliq], 2, 1) = "X"
UNION ALL
SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, 1) = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, 1) = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, 1) = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, 1) = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7 ,1) = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, 1) = "X"

<etc up through>
SELECT "P" FROM BENNETT WHERE Mid([p_aliq], 16, 1) = "X";

I must say this seems like a VERY shaky and peculiar way of entering data!
Whoever's typing X X X XX X X X will need a steady hand and some good way
to count blanks, if they are indeed typing it.

 

[Douglas J. Steele]

Change each of the subselects from

SELECT "A" FROM BENNETT WHERE Mid([p_aliq], 1, 1) = "X"

to

SELECT "A" FROM BENNETT WHERE Mid([p_aliq], 1, 1) = "X" OR [p_aliq] =
"ENTIRE SECTION"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

One last request. Some of the records contain "ENTIRE SECTION" in the
"p_aliq" field rather than "X". I need to have these records converted
into
16 records with the field "p_aliq" populated with "A" through "P".

Thanks again!

SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, "X") = "X"
UNION ALL

[quoted text clipped - 12 lines]

SELECT "J" FROM BENNETT WHERE Mid([p_aliq], 10, "X") = "X"
UNION ALL

Actually a couple of errors there. The "X" in the third argument to Mid()
should be a 1 (to extract one byte); and (if you don't already have one)
you
need an A, B and on through P. As Douglas says you also need to get rid of
the
hanging UNION ALL at the end:

SELECT "A" FROM BENNETT WHERE Mid([p_aliq], 1, 1) = "X"
UNION ALL
SELECT "B" FROM BENNETT WHERE Mid([p_aliq], 2, 1) = "X"
UNION ALL
SELECT "C" FROM BENNETT WHERE Mid([p_aliq], 3, 1) = "X"
UNION ALL
SELECT "D" FROM BENNETT WHERE Mid([p_aliq], 4, 1) = "X"
UNION ALL
SELECT "E" FROM BENNETT WHERE Mid([p_aliq], 5, 1) = "X"
UNION ALL
SELECT "F" FROM BENNETT WHERE Mid([p_aliq], 6, 1) = "X"
UNION ALL
SELECT "G" FROM BENNETT WHERE Mid([p_aliq], 7 ,1) = "X"
UNION ALL
SELECT "H" FROM BENNETT WHERE Mid([p_aliq], 8, 1) = "X"

<etc up through>
SELECT "P" FROM BENNETT WHERE Mid([p_aliq], 16, 1) = "X";

I must say this seems like a VERY shaky and peculiar way of entering data!
Whoever's typing X X X XX X X X will need a steady hand and some good
way
to count blanks, if they are indeed typing it.