Joe0
asked on
Append query
I have a table of questions with a DATE_LAST_USED field, I would like to extract 10 questions randomly and then change the value of the DATE_LAST_USED for these fields to the current date.
I currently have this:
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY Rnd([tbl_QUESTIONS].[QUEST
If I click on the 'View' button, then I get a list of ten random records and the current date, which is what I would expect to happen! :)
Except if I click on 'Run', then it says that it didnt add 10 records due to key violations, so I have tried removing the QUESTION_ID from the query which then dosent respond with any errors, just adds ten blank records (with the QUESTION_ID, which is an autonumber).
Thank you in advance for any help.
Joe.
I currently have this:
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY Rnd([tbl_QUESTIONS].[QUEST
If I click on the 'View' button, then I get a list of ten random records and the current date, which is what I would expect to happen! :)
Except if I click on 'Run', then it says that it didnt add 10 records due to key violations, so I have tried removing the QUESTION_ID from the query which then dosent respond with any errors, just adds ten blank records (with the QUESTION_ID, which is an autonumber).
Thank you in advance for any help.
Joe.
Your query is an append query. Change it to an update query.
It's not that easy.
You'll need to create a field in the table and insert into that field a random number.
UPDATE tbl_QUESTIONS SET QUESTION_ID_random = rnd([QUESTION_ID]);
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY QUESTION_ID_random;
Nic;o)
You'll need to create a field in the table and insert into that field a random number.
UPDATE tbl_QUESTIONS SET QUESTION_ID_random = rnd([QUESTION_ID]);
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY QUESTION_ID_random;
Nic;o)
ASKER
Thanks for your quick response!
I have tried to copy and paste that code, got the error about having a semi-colon at end of the first line, so got rid of that, but then got a syntax error.
Im sorry, but im quite new to this, would be grateful for futher help... :)
I have tried to copy and paste that code, got the error about having a semi-colon at end of the first line, so got rid of that, but then got a syntax error.
Im sorry, but im quite new to this, would be grateful for futher help... :)
UPDATE tbl_QUESTIONS SET QUESTION_ID_random = rnd([QUESTION_ID])
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY QUESTION_ID_random;
credit goes to Nico
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY QUESTION_ID_random;
credit goes to Nico
ASKER
yeah, thats what i tried, still gets a syntax error from the rnd([QUESTION_ID]).
Thanks anyway.
Thanks anyway.
You'll need to create TWO queries (UPDATE and INSERT) and run them in sequence.
Nic;o)
Nic;o)
ASKER
Oh yeah, sorry.
I tried that, but still geting same stuff back...
If i leave the QUESTION_ID in, then it wont work, if i remove it from the query, then I get blank records (except for QUESTION_ID).
Im assuming that QUESTION_ID_RND must be a double.
Thanks your help...
I tried that, but still geting same stuff back...
If i leave the QUESTION_ID in, then it wont work, if i remove it from the query, then I get blank records (except for QUESTION_ID).
Im assuming that QUESTION_ID_RND must be a double.
Thanks your help...
ASKER
maybe i should rephrase the question:
i need a query that will randomly select 10 records and update a field in each of these records.
please help me!
thanks
i need a query that will randomly select 10 records and update a field in each of these records.
please help me!
thanks
Hey Joe! Getting 'random' records from a table goes against the grain of the database concept. Not impossible to do, but you'll need to figure out how to identify random records. For example, if this table has a numeric ID field, you could (in a function) use the rnd function based on the min and max ID values to return a random ID. Looping through this ten times would generate ten 'random' id's. It is possible, although unlikely that you would get a duplicate, but it is possible. To prevent this, you would want to compare the just gotten ID from the previous ones. If it was a dupe, you'd discard that and try again until you got ten unique, random IDs
With these ten random ID's, you would then use them as criuteria in your update query.
Is this what you are looking for?
With these ten random ID's, you would then use them as criuteria in your update query.
Is this what you are looking for?
ASKER
yeah, i think that is sort of what i need.
i can select the random records fine when using an append query, but i think that i have to use an update query, so cannot select the top 10. This is the code that I have been trying to use:
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY Rnd([tbl_QUESTIONS].[QUEST
the problem is that you do not seem to be able to use SELECT TOP 10 or ORDER BY in an update query.
thankyou for your help so far.
joe.
i can select the random records fine when using an append query, but i think that i have to use an update query, so cannot select the top 10. This is the code that I have been trying to use:
INSERT INTO tbl_QUESTIONS ( QUESTION_ID, DATE_LAST_USED )
SELECT TOP 10 tbl_QUESTIONS.QUESTION_ID,
FROM tbl_QUESTIONS
ORDER BY Rnd([tbl_QUESTIONS].[QUEST
the problem is that you do not seem to be able to use SELECT TOP 10 or ORDER BY in an update query.
thankyou for your help so far.
joe.
Are you satisfied with the 'top ten' instead of actual random records?
ASKER
they would be random because they are being randomly ordered, so the top ten would be random, the problem being that this method does not work when using an update query...as far as i can understand.
thankyou. joe.
thankyou. joe.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much!!!
I had managed to find a way by making a temporary table, but this works much better!
I had managed to find a way by making a temporary table, but this works much better!
Plus, you will not suffer exxcesive database bloat from temp tables. Congrats!