CODEWITHSUNDEEP
sqlms-access
Dave Babbitt
Dave Babbitt

Reputation: 1080

Random Doubled Results in MS Access Query

If I run this query in MS Access:

SELECT q2.questionText, Answers.answerText
FROM Questions q2 INNER JOIN Answers ON q2.questionID = Answers.questionID
WHERE q2.questionID IN (SELECT TOP 15 q.questionID
FROM Questions q
ORDER BY RND(q.questionID));

I sometimes get doubled-up answerText rows. If I make a list of number from the output of this:

SELECT TOP 15 a.questionID
FROM Questions a
ORDER BY RND(a.questionID)

And use the numbers in the super query above:

SELECT q2.questionText, Answers.answerText
FROM Questions q2 INNER JOIN Answers ON q2.questionID = Answers.questionID
WHERE q2.questionID IN (125, 7, 44, 119, 85, 189, 159, 167, 11, 50, 23, 96, 18, 99, 121);

it works as expected. I'm enforcing referential integrity on the two questionID fields. I have been repeatedly deleting the tables and importing them again from a text file export that I added rows to. How to fix?

Upvotes: 1

Views: 168

Answers (2)

Dave Babbitt
Dave Babbitt

Reputation: 1080

The "repeatedly deleting the tables and importing them again from a text file export" thing caused duplications in the answers, which messed up the results. Removing the duplicates in the Answers table fixed the problem. Sorry for wasting your valuable time; I just didn't think of checking that until now.

Upvotes: 0

BaconSah
BaconSah

Reputation: 421

To me, the RND(a.QuestionID) could give the same number on more than one occasion. I would also suggest that "I sometimes get doubled-up answerText" means it works some of the time, and the second query has just worked by chance.

Perhaps something:

SELECT TOP 15 questionID FROM ( SELECT DISTINCT TOP 30 a.questionID FROM Questions a ) ORDER BY RND(questionID)

Might help, but it still will not guarantee that you get multiples of the same questionid, just it has a very much reduced chance and thus you should be able to get at least 15 questions out of the distinctly random generated set.

Upvotes: 1

Related Questions