473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Filling question ID automatically by using query or VBA

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
Nov 12 '05 #1
3 3031
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


Nov 12 '05 #2
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

Nov 12 '05 #3
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


Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Aravind | last post by:
Hi folks. I have the following tables (PK = primary key, FK = foreign key): Member (MemNo , MemName, MemType, Course/Faculty) History (..., MemNo , ...) Member and History are linked in a 1...
5
by: -elniniol999- | last post by:
I am DESPERATE for a quick reply.. have exhausted my options... i have a table called order details which contains: order id product id product name quantity unit price sale price
6
by: Brian | last post by:
Hello, Basically, I'm running a query on a form's activation, and I'd like to have the results of the query be placed into other fields on the same form automatically. Does anybody know how...
1
by: gaucho | last post by:
Hi all, I'm experiencing some problems when filling in bookmarks in word. With my first query (single row returned), no problem at all. Yet, with my new query (which now return 2 rows), i get...
3
by: crjunk | last post by:
I have a 3 table in my DataSet that I'm filling with data. After I've filled these 3 tables, I'm then trying to run a query that will fill a 4th table in the DataSet with data from the three...
11
by: Brian Henry | last post by:
Well here is the problem, I have a data set with about 9,000 to 20,000 people in it in the data table "people"... I am then reading it into a list view one at a time row by row... adding each...
4
by: Sommerfield | last post by:
I've imported a txt list into Access which has some rows that look like: 0000 0100 CH Saze National Msr 1 M CH 4460b 4750x 4800q 5945b 6030b 6125s 7275b 7290b 9500s 9630q 9675b...
4
by: Kun | last post by:
i have a form which takes in inputs for a mysql query. one of the inputs is 'date'. normally, a user has to manually enter a date, but i am wondering if there is a way to create a button which...
2
by: KDawg44 | last post by:
Hi, I am writing a PHP website. When I am taking some input from a user, I write some static radio buttons for a category, and then also write some more that are generated based on a query to a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.