Counting deliminators in a text value

[Viperpurple]

Hey Peeps,

I'm designing a database that requires the user to fill in a text box with
as many answers as they can think of seperated by commas. I do realise that
giving them lots of text boxes might be better but its not possible. I have
two requests that i need help with.

1. I'd like to count the number of deliminaters in the text box, hence the
number of answers (roughly)

2. Ideally i'd like to take each answer and dynamically create a sub table
that lists all the answers in as columns (this is way beyond my knowledge but
i'm a very quick learner)

Any help is appreciated, hope it all makes sense, any questions just post
them

TTFN :-þ

Adam

 

[KARL DEWEY]

There is no need to give them lots of text boxes.
Add a subform in datasheet display using another table with a one-to-many
relationship from question table to answer table.

 

[peregenem]

1. I'd like to count the number of deliminaters in the text box, hence the
number of answers (roughly)

2. Ideally i'd like to take each answer and dynamically create a sub table
that lists all the answers in as columns (this is way beyond my knowledge but
i'm a very quick learner)

You first need to learn to ask the correction question <g>. Building
tables on the fly means you have no data model. And you'll be wanting
to create one row for each answer.

One (recycled, thanks Joe Celko) answer is to have one table
InputStrings to take the delimited strings and another table ParmList
into which the parsed answers will go. The parsing can be done by a
procedure (a.k.a. Query object), so you have a completely 'relational'
solution i.e. no custom VBA/procedural code, can be used outside of the
Access app, etc. This example uses INTEGER data but is easily modified
for text:

CREATE TABLE InputStrings (
keycol VARCHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL)
;

Some test data:

INSERT INTO InputStrings
VALUES ('first', '12,34,567,896')
;
INSERT INTO InputStrings
VALUES ('second', '312,534,997,896')
;
INSERT INTO InputStrings
VALUES ('Third', '667,841')
;
INSERT INTO InputStrings
VALUES ('Forth', '523,842,225,227,458,369')
;
CREATE TABLE Parmlist (
keycol VARCHAR(10) NOT NULL,
parm INTEGER NOT NULL)
;

Use the standard 'sequence' table (table of incrementing integers) we
all have:

CREATE TABLE Sequence (seq INTEGER NOT NULL)
;
INSERT INTO Sequence VALUES (1)
;
INSERT INTO Sequence VALUES (2)
;
INSERT INTO Sequence VALUES (3)
;
....
INSERT INTO Sequence VALUES (999)
;

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CLNG(MID$(I1.input_string, S1.seq, MIN(S2.seq) - S1.seq - 1))
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE MID$(',' & I1.input_string & ',', S1.seq, 1) =
','
AND MID$(',' & I1.input_string & ',', S2.seq, 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq
;