Filling question ID automatically by using query or VBA

[Ekqvist Marko]

Hi,
I have one Access database table including questions and answers. Now
I need to give answer id automatically to questionID column. But I
don't know how it is best (fastest) to do?

table before
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 answer to the question 06f
1595 12 answer 06f continue to the question 06f
1596 12 answer 06f continue to the question 06f
1597 12 06g 06g 06g new question
1598 12 answer 06g continue
1599 12 answer 06g continue
1600 12 07 07 07 question
1601 12 answer 07
1602 12 answer 07 continue
1603 12 answer 07 continue
1604 12 answer 07 continue

table after (How can I do that with query or VBA?)
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 06f answer to the question 06f
1595 12 06f answer 06f continue to the question 06f
1596 12 06f answer 06f continue to the question 06f
1597 12 06g 06g 06g new question
1598 12 06g answer 06g continue
1599 12 06g answer 06g continue
1600 12 07 07 07 question
1601 12 07 answer 07
1602 12 07 answer 07 continue
1603 12 07 answer 07 continue
1604 12 07 answer 07 continue
column qryrow indicates only in which row inlcude question and my
probelm is how I could fill column questionID rows with correct
questionID automatically. (total rows count 150000).

Kind Regards,
Marko

[Larry Johnson]

While I'm not sure how to approach it, some questions come to mind.
How many questions are there (06e, 06f, 07, etc.)? Is this a one-time
project to fill the questionID column? In the future can questionID be
filled as new rows are added?

On 2 Mar 2004 01:31:30 -0800, ma***********@hotmail.com (Ekqvist
Marko) wrote:

Hi,
I have one Access database table including questions and answers. Now
I need to give answer id automatically to questionID column. But I
don't know how it is best (fastest) to do?

table before
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 answer to the question 06f
1595 12 answer 06f continue to the question 06f
1596 12 answer 06f continue to the question 06f
1597 12 06g 06g 06g new question
1598 12 answer 06g continue
1599 12 answer 06g continue
1600 12 07 07 07 question
1601 12 answer 07
1602 12 answer 07 continue
1603 12 answer 07 continue
1604 12 answer 07 continue

table after (How can I do that with query or VBA?)
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 06f answer to the question 06f
1595 12 06f answer 06f continue to the question 06f
1596 12 06f answer 06f continue to the question 06f
1597 12 06g 06g 06g new question
1598 12 06g answer 06g continue
1599 12 06g answer 06g continue
1600 12 07 07 07 question
1601 12 07 answer 07
1602 12 07 answer 07 continue
1603 12 07 answer 07 continue
1604 12 07 answer 07 continue
column qryrow indicates only in which row inlcude question and my
probelm is how I could fill column questionID rows with correct
questionID automatically. (total rows count 150000).

Kind Regards,
Marko

[Ekqvist Marko]

Larry Johnson <la***@gjerager.com> wrote in message news:<59********************************@4ax.com>. ..

While I'm not sure how to approach it, some questions come to mind.
How many questions are there (06e, 06f, 07, etc.)? 60

Is this a one-time project to fill the questionID column?
At this moment one-time,

In the future can questionID be filled as new rows are added?

On 2 Mar 2004 01:31:30 -0800, ma***********@hotmail.com (Ekqvist
Marko) wrote:

Hi,
I have one Access database table including questions and answers. Now
I need to give answer id automatically to questionID column. But I
don't know how it is best (fastest) to do?

table before
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 answer to the question 06f
1595 12 answer 06f continue to the question 06f
1596 12 answer 06f continue to the question 06f
1597 12 06g 06g 06g new question
1598 12 answer 06g continue
1599 12 answer 06g continue
1600 12 07 07 07 question
1601 12 answer 07
1602 12 answer 07 continue
1603 12 answer 07 continue
1604 12 answer 07 continue

table after (How can I do that with query or VBA?)
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 06f answer to the question 06f
1595 12 06f answer 06f continue to the question 06f
1596 12 06f answer 06f continue to the question 06f
1597 12 06g 06g 06g new question
1598 12 06g answer 06g continue
1599 12 06g answer 06g continue
1600 12 07 07 07 question
1601 12 07 answer 07
1602 12 07 answer 07 continue
1603 12 07 answer 07 continue
1604 12 07 answer 07 continue
column qryrow indicates only in which row inlcude question and my
probelm is how I could fill column questionID rows with correct
questionID automatically. (total rows count 150000).

Kind Regards,
Marko

[Larry Johnson]

Using a query like this would work:

UPDATE table SET questionID = '06e' WHERE InStr(datafield, '06e')>0;

Unfortunately you'd have to do this 60 times. Plan B would be to build
a table with the question numbers in it, open a recordset on the
table, then walk the recordset, building and running the query:

rst is a recordset containing one field named quastionID, contrining
the codes, 06a, 06b, etc., created however you do it depending on
version.

docmd.setwarnings false
rst.movefirst
while not rst.eof
SQLCmd="UPDATE table SET questionID = '" & rst("questionID") & " '
WHERE InStr(datafield,'" & rst("questionID") & "') > 0;"
docmd.runsql SQLCmd
rst.movenext
wend
docmd.setwarnings true

Be careful with the quotes; the questionID should be surrounded by
single quotes, like '06e'.
On 5 Mar 2004 01:26:48 -0800, ma***********@hotmail.com (Ekqvist
Marko) wrote:

Larry Johnson <la***@gjerager.com> wrote in message news:<59********************************@4ax.com>. ..

While I'm not sure how to approach it, some questions come to mind.
How many questions are there (06e, 06f, 07, etc.)?

60

Is this a one-time

project to fill the questionID column?

At this moment one-time,

In the future can questionID be

filled as new rows are added?

On 2 Mar 2004 01:31:30 -0800, ma***********@hotmail.com (Ekqvist
Marko) wrote:

>Hi,
>I have one Access database table including questions and answers. Now
>I need to give answer id automatically to questionID column. But I
>don't know how it is best (fastest) to do?
>
>table before
>rowID answID qryrow questionID datafield
>1591 12 06e 06e 06e question
>1593 12 06f 06f 06f question
>1594 12 answer to the question 06f
>1595 12 answer 06f continue to the question 06f
>1596 12 answer 06f continue to the question 06f
>1597 12 06g 06g 06g new question
>1598 12 answer 06g continue
>1599 12 answer 06g continue
>1600 12 07 07 07 question
>1601 12 answer 07
>1602 12 answer 07 continue
>1603 12 answer 07 continue
>1604 12 answer 07 continue
>
>table after (How can I do that with query or VBA?)
>rowID answID qryrow questionID datafield
>1591 12 06e 06e 06e question
>1593 12 06f 06f 06f question
>1594 12 06f answer to the question 06f
>1595 12 06f answer 06f continue to the question 06f
>1596 12 06f answer 06f continue to the question 06f
>1597 12 06g 06g 06g new question
>1598 12 06g answer 06g continue
>1599 12 06g answer 06g continue
>1600 12 07 07 07 question
>1601 12 07 answer 07
>1602 12 07 answer 07 continue
>1603 12 07 answer 07 continue
>1604 12 07 answer 07 continue
>
>
>column qryrow indicates only in which row inlcude question and my
>probelm is how I could fill column questionID rows with correct
>questionID automatically. (total rows count 150000).
>
>Kind Regards,
>Marko