random sort my table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK i have search thru thends of this forums and i can not figure out how to
use the RND function.

Please be specific when responding to this

I have 6 tables, each consist of 8 fields, question, question #, a answer, b
anwesr etc. I want to make a 100 question test based off the data in my
tables. Each table is only used to make 1 test. So we dont have to worry
about using multple tables.

I am pretty new to access and got the basic stuff down.

I figured out how to make a append query to move my top 100 question to
another table which is used to generate the test. Made my reports to print
out the test and the answer key. Also made a delete quesry to delete the
data in this table.

I need to figure out how to randomly sort the tables by question #'s.
Please be detailed in the response.

Thanks to all who respond to this.
 
Dear Adam:

To me, your question is confusing. If you want to sort your rows randomly,
then they would not be sorted randomly "by question #'s." Random is random,
and is not "by" anything.

Perhaps you are not getting any random numbers generated. The RND()
function requires a seed in order to generate varying values in a query.
Use Rnd([question #]) for example. Perhaps:

SELECT TOP 100 *
FROM YourTable
ORDER BY Rnd([question #])

would do it.

Tom Ellison
 
I agree with Tom's answer, but have added an example. Like Tom, I
didn't understand your question. Do you want a random selection of 100
questions from a set of 500? Do you want a random subset of 4 of the 8
answers in your Table?

Anyway, what I came up with was the following. And I included far fewer
than 100 records (sorry). You can add more by following the pattern.

First, I suggest not putting several fields of the same type (such as
answer choices) into the same record. Instead, you can put the question
numbers and other stuff relating to the entire question into a
[Question] Table, like this:

[Question] Table Datasheet View:

Question_ID Question Question # Test #
----------- --------------- ---------- ------
575532147 Favorite color? 2 1
696402796 Who am I? 1 1

I included [Test #] to let you specify to which of the 8 tests the
question belongs. I see no need to maintain 8 separate Tables for this.
You'll save work by combining them, I think, since adding or changing
a field will need to be done only once instead of 8 times.

The answers can go into an [Answer] Table, linked to [Question] via a
"foreign key" that matches a record in [Question]. (It's called a
"primary key" in the [Question] Table, since it identifies a record
there. In this Table it just refers to that.)

[Answer] Table Datasheet View:
Answer_ID Choice Answer Question_ID
---------- ------ --------------- -----------
326689277 a Rumplestiltskin 696402796
669114936 a Red 575532147
1435043706 b Hercules 696402796
1777538009 b Green 575532147
2093970214 c Mauve 575532147

To make this easier to read, I defined a Lookup property on the
[Answer].[Question_ID] field, so that the foreign key number is hidden.
It's still what's stored in the Table, but what you see is the
[Question] field from the [Question] Table.

[Answer] Table Datasheet View, with Lookup property set on [Question_ID]
field:

Answer_ID Choice Answer Question_ID
----------- ------ --------------- ----------------
326689277 a Rumplestiltskin Who am I?
669114936 a Red Favorite color?
1435043706 b Hercules Who am I?
1777538009 b Green Favorite color?
2093970214 c Mauve Favorite color?

Now all the choices are defined for all two of our test questions. To
display them, we can define a Query, and it will display only questions
from Test 1 (which we identified by setting [Question].[Test #] equal to
1 in all of the records for Test 1). For Test 2, you'd add questions to
this same Table, but with [Test #] = 2.

[Q_AllAnswers] SQL:

SELECT Question.[Test #], Question.[Question #],
Question.Question, "(" & [Choice] & ")" AS Label,
Answer.Answer
FROM Question INNER JOIN Answer
ON Question.Question_ID = Answer.Question_ID
WHERE (((Question.[Test #])=1))
ORDER BY Question.[Question #],
"(" & [Choice] & ")";

This Query lists all the questions for Test 1, in order, with the
related answers. All of these records could have come from a Table, but
you'd have a harder time maintaining the data. Here, if you discover a
misspelling in a [Question] field, you can correct it the one time it
occurs and all of the places that it might appear are automatically updated.

[Q_AllAnswers] Query Datasheet View:

Test # Question # Question Label Answer
------- ---------- --------------- ------ ---------------
1 1 Who am I? (a) Rumplestiltskin
1 1 Who am I? (b) Hercules
1 2 Favorite color? (a) Red
1 2 Favorite color? (b) Green
1 2 Favorite color? (c) Mauve

Now, you may not be happy with this format. The next Query lists each
question just once, followed by its choices. You could use this as the
basis for a Report, instead of creating a new Table. (If you really are
possessed to create a separate Table, you can define a Make-Table Query
that gets its records from this Query.)

[Q_AllAnswers_Crosstab] SQL:

TRANSFORM First(Q_AllAnswers.Answer) AS FirstOfAnswer
SELECT Q_AllAnswers.[Question #], Q_AllAnswers.Question
FROM Q_AllAnswers
GROUP BY Q_AllAnswers.[Question #], Q_AllAnswers.Question
PIVOT Q_AllAnswers.Label;

The results look like this. You might notice that there are only 2
records here, not 100, but if you'd put 100 records into your [Question]
Table, you would have gotten 100 records here.

[Q_AllAnswers_Crosstab] Query Datasheet View:

Ques Question (a) (b) (c)
tion #
------ --------------- --------------- -------- -----
1 Who am I? Rumplestiltskin Hercules
2 Favorite color? Red Green Mauve

Now, as Tom Ellison suggested, you could select a random subset of these
via a Query like this:

[Q_RandomQuestion] SQL:

SELECT TOP 1 Q_AllAnswers_Crosstab.*
FROM Q_AllAnswers_Crosstab
ORDER BY Rnd([Question #]);

Running this Query generates only 1 record, not 100, but you can correct
that by changing the number following "TOP" in the SQL. Of course, the
output from this is not consistent, but when you run it you might get this:

[Q_RandomQuestion] Query Datasheet View (sometimes):

Question # Question (a) (b) (c)
---------- --------------- --- ----- -----
2 Favorite color? Red Green Mauve


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Tom said:
Dear Adam:

To me, your question is confusing. If you want to sort your rows randomly,
then they would not be sorted randomly "by question #'s." Random is random,
and is not "by" anything.

Perhaps you are not getting any random numbers generated. The RND()
function requires a seed in order to generate varying values in a query.
Use Rnd([question #]) for example. Perhaps:

SELECT TOP 100 *
FROM YourTable
ORDER BY Rnd([question #])

would do it.

Tom Ellison


OK i have search thru thends of this forums and i can not figure out how
to
use the RND function.

Please be specific when responding to this

I have 6 tables, each consist of 8 fields, question, question #, a answer,
b
anwesr etc. I want to make a 100 question test based off the data in my
tables. Each table is only used to make 1 test. So we dont have to worry
about using multple tables.

I am pretty new to access and got the basic stuff down.

I figured out how to make a append query to move my top 100 question to
another table which is used to generate the test. Made my reports to
print
out the test and the answer key. Also made a delete quesry to delete the
data in this table.

I need to figure out how to randomly sort the tables by question #'s.
Please be detailed in the response.

Thanks to all who respond to this.
Click to expand...
Click to expand...
 
OK sorry about the confusion on this. Lets work with one table that i have
already.

My fields are

question #, Volume, question, a, b, c, d, and correct answer. In this table
there are 644 records (questions 1 thru 644). I thought there was a way to
shuffle (hopefully a better word than random) my table.

I want to shuffle these records in no specific manner. then move it to
another table, to generate my test of a 100 questions. I have the query made
to move the information from this table to the next and only take the top
100.

Like i said i am a beginner at this so please be patient with me. If you
would like me to send a copy of the database to you i will so you can
physically see what i am doing.

Thanks again

Vincent Johns said:
I agree with Tom's answer, but have added an example. Like Tom, I
didn't understand your question. Do you want a random selection of 100
questions from a set of 500? Do you want a random subset of 4 of the 8
answers in your Table?

Anyway, what I came up with was the following. And I included far fewer
than 100 records (sorry). You can add more by following the pattern.

First, I suggest not putting several fields of the same type (such as
answer choices) into the same record. Instead, you can put the question
numbers and other stuff relating to the entire question into a
[Question] Table, like this:

[Question] Table Datasheet View:

Question_ID Question Question # Test #
----------- --------------- ---------- ------
575532147 Favorite color? 2 1
696402796 Who am I? 1 1

I included [Test #] to let you specify to which of the 8 tests the
question belongs. I see no need to maintain 8 separate Tables for this.
You'll save work by combining them, I think, since adding or changing
a field will need to be done only once instead of 8 times.

The answers can go into an [Answer] Table, linked to [Question] via a
"foreign key" that matches a record in [Question]. (It's called a
"primary key" in the [Question] Table, since it identifies a record
there. In this Table it just refers to that.)

[Answer] Table Datasheet View:
Answer_ID Choice Answer Question_ID
---------- ------ --------------- -----------
326689277 a Rumplestiltskin 696402796
669114936 a Red 575532147
1435043706 b Hercules 696402796
1777538009 b Green 575532147
2093970214 c Mauve 575532147

To make this easier to read, I defined a Lookup property on the
[Answer].[Question_ID] field, so that the foreign key number is hidden.
It's still what's stored in the Table, but what you see is the
[Question] field from the [Question] Table.

[Answer] Table Datasheet View, with Lookup property set on [Question_ID]
field:

Answer_ID Choice Answer Question_ID
----------- ------ --------------- ----------------
326689277 a Rumplestiltskin Who am I?
669114936 a Red Favorite color?
1435043706 b Hercules Who am I?
1777538009 b Green Favorite color?
2093970214 c Mauve Favorite color?

Now all the choices are defined for all two of our test questions. To
display them, we can define a Query, and it will display only questions
from Test 1 (which we identified by setting [Question].[Test #] equal to
1 in all of the records for Test 1). For Test 2, you'd add questions to
this same Table, but with [Test #] = 2.

[Q_AllAnswers] SQL:

SELECT Question.[Test #], Question.[Question #],
Question.Question, "(" & [Choice] & ")" AS Label,
Answer.Answer
FROM Question INNER JOIN Answer
ON Question.Question_ID = Answer.Question_ID
WHERE (((Question.[Test #])=1))
ORDER BY Question.[Question #],
"(" & [Choice] & ")";

This Query lists all the questions for Test 1, in order, with the
related answers. All of these records could have come from a Table, but
you'd have a harder time maintaining the data. Here, if you discover a
misspelling in a [Question] field, you can correct it the one time it
occurs and all of the places that it might appear are automatically updated.

[Q_AllAnswers] Query Datasheet View:

Test # Question # Question Label Answer
------- ---------- --------------- ------ ---------------
1 1 Who am I? (a) Rumplestiltskin
1 1 Who am I? (b) Hercules
1 2 Favorite color? (a) Red
1 2 Favorite color? (b) Green
1 2 Favorite color? (c) Mauve

Now, you may not be happy with this format. The next Query lists each
question just once, followed by its choices. You could use this as the
basis for a Report, instead of creating a new Table. (If you really are
possessed to create a separate Table, you can define a Make-Table Query
that gets its records from this Query.)

[Q_AllAnswers_Crosstab] SQL:

TRANSFORM First(Q_AllAnswers.Answer) AS FirstOfAnswer
SELECT Q_AllAnswers.[Question #], Q_AllAnswers.Question
FROM Q_AllAnswers
GROUP BY Q_AllAnswers.[Question #], Q_AllAnswers.Question
PIVOT Q_AllAnswers.Label;

The results look like this. You might notice that there are only 2
records here, not 100, but if you'd put 100 records into your [Question]
Table, you would have gotten 100 records here.

[Q_AllAnswers_Crosstab] Query Datasheet View:

Ques Question (a) (b) (c)
tion #
------ --------------- --------------- -------- -----
1 Who am I? Rumplestiltskin Hercules
2 Favorite color? Red Green Mauve

Now, as Tom Ellison suggested, you could select a random subset of these
via a Query like this:

[Q_RandomQuestion] SQL:

SELECT TOP 1 Q_AllAnswers_Crosstab.*
FROM Q_AllAnswers_Crosstab
ORDER BY Rnd([Question #]);

Running this Query generates only 1 record, not 100, but you can correct
that by changing the number following "TOP" in the SQL. Of course, the
output from this is not consistent, but when you run it you might get this:

[Q_RandomQuestion] Query Datasheet View (sometimes):

Question # Question (a) (b) (c)
---------- --------------- --- ----- -----
2 Favorite color? Red Green Mauve


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Tom said:
Dear Adam:

To me, your question is confusing. If you want to sort your rows randomly,
then they would not be sorted randomly "by question #'s." Random is random,
and is not "by" anything.

Perhaps you are not getting any random numbers generated. The RND()
function requires a seed in order to generate varying values in a query.
Use Rnd([question #]) for example. Perhaps:

SELECT TOP 100 *
FROM YourTable
ORDER BY Rnd([question #])

would do it.

Tom Ellison


OK i have search thru thends of this forums and i can not figure out how
to
use the RND function.

Please be specific when responding to this

I have 6 tables, each consist of 8 fields, question, question #, a answer,
b
anwesr etc. I want to make a 100 question test based off the data in my
tables. Each table is only used to make 1 test. So we dont have to worry
about using multple tables.

I am pretty new to access and got the basic stuff down.

I figured out how to make a append query to move my top 100 question to
another table which is used to generate the test. Made my reports to
print
out the test and the answer key. Also made a delete quesry to delete the
data in this table.

I need to figure out how to randomly sort the tables by question #'s.
Please be detailed in the response.

Thanks to all who respond to this.
Click to expand...
Click to expand...
Click to expand...
 
Dear Adam:

I suggested before:

SELECT TOP 100 *
FROM YourTable
ORDER BY Rnd([question #])

Be sure to replace YourTable with the actual name of your table.

Did you try this? Is there some specific way in which this does not do what
you want?

I think a response to this would be more useful than trying to work through
a copy of your database. Why not give that a try?

Tom Ellison


ADAM said:
OK sorry about the confusion on this. Lets work with one table that i
have
already.

My fields are

question #, Volume, question, a, b, c, d, and correct answer. In this
table
there are 644 records (questions 1 thru 644). I thought there was a way
to
shuffle (hopefully a better word than random) my table.

I want to shuffle these records in no specific manner. then move it to
another table, to generate my test of a 100 questions. I have the query
made
to move the information from this table to the next and only take the top
100.

Like i said i am a beginner at this so please be patient with me. If you
would like me to send a copy of the database to you i will so you can
physically see what i am doing.

Thanks again

Vincent Johns said:
I agree with Tom's answer, but have added an example. Like Tom, I
didn't understand your question. Do you want a random selection of 100
questions from a set of 500? Do you want a random subset of 4 of the 8
answers in your Table?

Anyway, what I came up with was the following. And I included far fewer
than 100 records (sorry). You can add more by following the pattern.

First, I suggest not putting several fields of the same type (such as
answer choices) into the same record. Instead, you can put the question
numbers and other stuff relating to the entire question into a
[Question] Table, like this:

[Question] Table Datasheet View:

Question_ID Question Question # Test #
----------- --------------- ---------- ------
575532147 Favorite color? 2 1
696402796 Who am I? 1 1

I included [Test #] to let you specify to which of the 8 tests the
question belongs. I see no need to maintain 8 separate Tables for this.
You'll save work by combining them, I think, since adding or changing
a field will need to be done only once instead of 8 times.

The answers can go into an [Answer] Table, linked to [Question] via a
"foreign key" that matches a record in [Question]. (It's called a
"primary key" in the [Question] Table, since it identifies a record
there. In this Table it just refers to that.)

[Answer] Table Datasheet View:
Answer_ID Choice Answer Question_ID
---------- ------ --------------- -----------
326689277 a Rumplestiltskin 696402796
669114936 a Red 575532147
1435043706 b Hercules 696402796
1777538009 b Green 575532147
2093970214 c Mauve 575532147

To make this easier to read, I defined a Lookup property on the
[Answer].[Question_ID] field, so that the foreign key number is hidden.
It's still what's stored in the Table, but what you see is the
[Question] field from the [Question] Table.

[Answer] Table Datasheet View, with Lookup property set on [Question_ID]
field:

Answer_ID Choice Answer Question_ID
----------- ------ --------------- ----------------
326689277 a Rumplestiltskin Who am I?
669114936 a Red Favorite color?
1435043706 b Hercules Who am I?
1777538009 b Green Favorite color?
2093970214 c Mauve Favorite color?

Now all the choices are defined for all two of our test questions. To
display them, we can define a Query, and it will display only questions
from Test 1 (which we identified by setting [Question].[Test #] equal to
1 in all of the records for Test 1). For Test 2, you'd add questions to
this same Table, but with [Test #] = 2.

[Q_AllAnswers] SQL:

SELECT Question.[Test #], Question.[Question #],
Question.Question, "(" & [Choice] & ")" AS Label,
Answer.Answer
FROM Question INNER JOIN Answer
ON Question.Question_ID = Answer.Question_ID
WHERE (((Question.[Test #])=1))
ORDER BY Question.[Question #],
"(" & [Choice] & ")";

This Query lists all the questions for Test 1, in order, with the
related answers. All of these records could have come from a Table, but
you'd have a harder time maintaining the data. Here, if you discover a
misspelling in a [Question] field, you can correct it the one time it
occurs and all of the places that it might appear are automatically
updated.

[Q_AllAnswers] Query Datasheet View:

Test # Question # Question Label Answer
------- ---------- --------------- ------ ---------------
1 1 Who am I? (a) Rumplestiltskin
1 1 Who am I? (b) Hercules
1 2 Favorite color? (a) Red
1 2 Favorite color? (b) Green
1 2 Favorite color? (c) Mauve

Now, you may not be happy with this format. The next Query lists each
question just once, followed by its choices. You could use this as the
basis for a Report, instead of creating a new Table. (If you really are
possessed to create a separate Table, you can define a Make-Table Query
that gets its records from this Query.)

[Q_AllAnswers_Crosstab] SQL:

TRANSFORM First(Q_AllAnswers.Answer) AS FirstOfAnswer
SELECT Q_AllAnswers.[Question #], Q_AllAnswers.Question
FROM Q_AllAnswers
GROUP BY Q_AllAnswers.[Question #], Q_AllAnswers.Question
PIVOT Q_AllAnswers.Label;

The results look like this. You might notice that there are only 2
records here, not 100, but if you'd put 100 records into your [Question]
Table, you would have gotten 100 records here.

[Q_AllAnswers_Crosstab] Query Datasheet View:

Ques Question (a) (b) (c)
tion #
------ --------------- --------------- -------- -----
1 Who am I? Rumplestiltskin Hercules
2 Favorite color? Red Green Mauve

Now, as Tom Ellison suggested, you could select a random subset of these
via a Query like this:

[Q_RandomQuestion] SQL:

SELECT TOP 1 Q_AllAnswers_Crosstab.*
FROM Q_AllAnswers_Crosstab
ORDER BY Rnd([Question #]);

Running this Query generates only 1 record, not 100, but you can correct
that by changing the number following "TOP" in the SQL. Of course, the
output from this is not consistent, but when you run it you might get
this:

[Q_RandomQuestion] Query Datasheet View (sometimes):

Question # Question (a) (b) (c)
---------- --------------- --- ----- -----
2 Favorite color? Red Green Mauve


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Tom said:
Dear Adam:

To me, your question is confusing. If you want to sort your rows
randomly,
then they would not be sorted randomly "by question #'s." Random is
random,
and is not "by" anything.

Perhaps you are not getting any random numbers generated. The RND()
function requires a seed in order to generate varying values in a
query.
Use Rnd([question #]) for example. Perhaps:

SELECT TOP 100 *
FROM YourTable
ORDER BY Rnd([question #])

would do it.

Tom Ellison



OK i have search thru thends of this forums and i can not figure out
how
to
use the RND function.

Please be specific when responding to this

I have 6 tables, each consist of 8 fields, question, question #, a
answer,
b
anwesr etc. I want to make a 100 question test based off the data in
my
tables. Each table is only used to make 1 test. So we dont have to
worry
about using multple tables.

I am pretty new to access and got the basic stuff down.

I figured out how to make a append query to move my top 100 question to
another table which is used to generate the test. Made my reports to
print
out the test and the answer key. Also made a delete quesry to delete
the
data in this table.

I need to figure out how to randomly sort the tables by question #'s.
Please be detailed in the response.

Thanks to all who respond to this.
Click to expand...
Click to expand...
Click to expand...
 
Like Tom Ellison, I am somewhat curious about what your experience was
when you tried to do what we suggested. However, I wouldn't mind taking
a brief look at your database if you want to send me a copy. (I won't
have time to do a great deal to it, of course.) But if you can ALSO
describe what went wrong when you did what we suggested, that would be
helpful as well.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Tom said:
Dear Adam:

I suggested before:

SELECT TOP 100 *
FROM YourTable
ORDER BY Rnd([question #])

Be sure to replace YourTable with the actual name of your table.

Did you try this? Is there some specific way in which this does not do what
you want?

I think a response to this would be more useful than trying to work through
a copy of your database. Why not give that a try?

Tom Ellison


OK sorry about the confusion on this. Lets work with one table that i
have
already.

My fields are

question #, Volume, question, a, b, c, d, and correct answer. In this
table
there are 644 records (questions 1 thru 644). I thought there was a way
to
shuffle (hopefully a better word than random) my table.

I want to shuffle these records in no specific manner. then move it to
another table, to generate my test of a 100 questions. I have the query
made
to move the information from this table to the next and only take the top
100.

Like i said i am a beginner at this so please be patient with me. If you
would like me to send a copy of the database to you i will so you can
physically see what i am doing.

Thanks again

:

I agree with Tom's answer, but have added an example. Like Tom, I
didn't understand your question. Do you want a random selection of 100
questions from a set of 500? Do you want a random subset of 4 of the 8
answers in your Table?

Anyway, what I came up with was the following. And I included far fewer
than 100 records (sorry). You can add more by following the pattern.

First, I suggest not putting several fields of the same type (such as
answer choices) into the same record. Instead, you can put the question
numbers and other stuff relating to the entire question into a
[Question] Table, like this:

[Question] Table Datasheet View:

Question_ID Question Question # Test #
----------- --------------- ---------- ------
575532147 Favorite color? 2 1
696402796 Who am I? 1 1

I included [Test #] to let you specify to which of the 8 tests the
question belongs. I see no need to maintain 8 separate Tables for this.
You'll save work by combining them, I think, since adding or changing
a field will need to be done only once instead of 8 times.

The answers can go into an [Answer] Table, linked to [Question] via a
"foreign key" that matches a record in [Question]. (It's called a
"primary key" in the [Question] Table, since it identifies a record
there. In this Table it just refers to that.)

[Answer] Table Datasheet View:
Answer_ID Choice Answer Question_ID
---------- ------ --------------- -----------
326689277 a Rumplestiltskin 696402796
669114936 a Red 575532147
1435043706 b Hercules 696402796
1777538009 b Green 575532147
2093970214 c Mauve 575532147

To make this easier to read, I defined a Lookup property on the
[Answer].[Question_ID] field, so that the foreign key number is hidden.
It's still what's stored in the Table, but what you see is the
[Question] field from the [Question] Table.

[Answer] Table Datasheet View, with Lookup property set on [Question_ID]
field:

Answer_ID Choice Answer Question_ID
----------- ------ --------------- ----------------
326689277 a Rumplestiltskin Who am I?
669114936 a Red Favorite color?
1435043706 b Hercules Who am I?
1777538009 b Green Favorite color?
2093970214 c Mauve Favorite color?

Now all the choices are defined for all two of our test questions. To
display them, we can define a Query, and it will display only questions
from Test 1 (which we identified by setting [Question].[Test #] equal to
1 in all of the records for Test 1). For Test 2, you'd add questions to
this same Table, but with [Test #] = 2.

[Q_AllAnswers] SQL:

SELECT Question.[Test #], Question.[Question #],
Question.Question, "(" & [Choice] & ")" AS Label,
Answer.Answer
FROM Question INNER JOIN Answer
ON Question.Question_ID = Answer.Question_ID
WHERE (((Question.[Test #])=1))
ORDER BY Question.[Question #],
"(" & [Choice] & ")";

This Query lists all the questions for Test 1, in order, with the
related answers. All of these records could have come from a Table, but
you'd have a harder time maintaining the data. Here, if you discover a
misspelling in a [Question] field, you can correct it the one time it
occurs and all of the places that it might appear are automatically
updated.

[Q_AllAnswers] Query Datasheet View:

Test # Question # Question Label Answer
------- ---------- --------------- ------ ---------------
1 1 Who am I? (a) Rumplestiltskin
1 1 Who am I? (b) Hercules
1 2 Favorite color? (a) Red
1 2 Favorite color? (b) Green
1 2 Favorite color? (c) Mauve

Now, you may not be happy with this format. The next Query lists each
question just once, followed by its choices. You could use this as the
basis for a Report, instead of creating a new Table. (If you really are
possessed to create a separate Table, you can define a Make-Table Query
that gets its records from this Query.)

[Q_AllAnswers_Crosstab] SQL:

TRANSFORM First(Q_AllAnswers.Answer) AS FirstOfAnswer
SELECT Q_AllAnswers.[Question #], Q_AllAnswers.Question
FROM Q_AllAnswers
GROUP BY Q_AllAnswers.[Question #], Q_AllAnswers.Question
PIVOT Q_AllAnswers.Label;

The results look like this. You might notice that there are only 2
records here, not 100, but if you'd put 100 records into your [Question]
Table, you would have gotten 100 records here.

[Q_AllAnswers_Crosstab] Query Datasheet View:

Ques Question (a) (b) (c)
tion #
------ --------------- --------------- -------- -----
1 Who am I? Rumplestiltskin Hercules
2 Favorite color? Red Green Mauve

Now, as Tom Ellison suggested, you could select a random subset of these
via a Query like this:

[Q_RandomQuestion] SQL:

SELECT TOP 1 Q_AllAnswers_Crosstab.*
FROM Q_AllAnswers_Crosstab
ORDER BY Rnd([Question #]);

Running this Query generates only 1 record, not 100, but you can correct
that by changing the number following "TOP" in the SQL. Of course, the
output from this is not consistent, but when you run it you might get
this:

[Q_RandomQuestion] Query Datasheet View (sometimes):

Question # Question (a) (b) (c)
---------- --------------- --- ----- -----
2 Favorite color? Red Green Mauve


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Tom Ellison wrote:


Dear Adam:

To me, your question is confusing. If you want to sort your rows
randomly,
then they would not be sorted randomly "by question #'s." Random is
random,
and is not "by" anything.

Perhaps you are not getting any random numbers generated. The RND()
function requires a seed in order to generate varying values in a
query.
Use Rnd([question #]) for example. Perhaps:

SELECT TOP 100 *
FROM YourTable
ORDER BY Rnd([question #])

would do it.

Tom Ellison




OK i have search thru thends of this forums and i can not figure out
how
to
use the RND function.

Please be specific when responding to this

I have 6 tables, each consist of 8 fields, question, question #, a
answer,
b
anwesr etc. I want to make a 100 question test based off the data in
my
tables. Each table is only used to make 1 test. So we dont have to
worry
about using multple tables.

I am pretty new to access and got the basic stuff down.

I figured out how to make a append query to move my top 100 question to
another table which is used to generate the test. Made my reports to
print
out the test and the answer key. Also made a delete quesry to delete
the
data in this table.

I need to figure out how to randomly sort the tables by question #'s.
Please be detailed in the response.

Thanks to all who respond to this.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

random sort 1
MAKING A QUERY TO RANDOMLY MAKE A 100 QUESTION TEST 6
Update Query Randomization 6
Something missing in my query? 1
Make table query: [Enter 1] AS Test 1
Random Record Selection 1
Multiple Choice 3
Quiz Test Maker 4

Back
Top