?
Solved

Append query

Posted on 2003-03-18
15
Medium Priority
?
228 Views
Last Modified: 2006-11-17
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, Date() AS Expr1
 FROM tbl_QUESTIONS
 ORDER BY Rnd([tbl_QUESTIONS].[QUESTION_ID]);

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.
0
Comment
Question by:Joe0
  • 7
  • 6
  • 2
15 Comments
 
LVL 18

Expert Comment

by:1William
ID: 8161076
Your query is an append query.  Change it to an update query.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8161106
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, Date() AS Expr1
FROM tbl_QUESTIONS
ORDER BY QUESTION_ID_random;

Nic;o)
0
 

Author Comment

by:Joe0
ID: 8161142
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... :)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 18

Expert Comment

by:1William
ID: 8161176
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, Date() AS Expr1
FROM tbl_QUESTIONS
ORDER BY QUESTION_ID_random;


credit goes to Nico
0
 

Author Comment

by:Joe0
ID: 8161189
yeah, thats what i tried, still gets a syntax error from the rnd([QUESTION_ID]).
Thanks anyway.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8161198
You'll need to create TWO queries (UPDATE and INSERT) and run them in sequence.

Nic;o)
0
 

Author Comment

by:Joe0
ID: 8161228
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...
0
 

Author Comment

by:Joe0
ID: 8161452
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
0
 
LVL 18

Expert Comment

by:1William
ID: 8161748
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?
0
 

Author Comment

by:Joe0
ID: 8161791
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, Date() AS Expr1
 FROM tbl_QUESTIONS
 ORDER BY Rnd([tbl_QUESTIONS].[QUESTION_ID]);

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.
0
 
LVL 18

Expert Comment

by:1William
ID: 8161808
Are you satisfied with the 'top ten' instead of actual random records?
0
 

Author Comment

by:Joe0
ID: 8161822
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.
0
 
LVL 18

Accepted Solution

by:
1William earned 160 total points
ID: 8161987
Joe, two queries.
query 1 called "qry_RandomIDs"
SELECT TOP 10 QUESTION_ID
FROM tbl_QUESTIONS
ORDER BY Rnd([tbl_QUESTIONS].[QUESTION_ID]);

query 2
UPDATE qry_RandomIDs INNER JOIN tbl_QUESTIONS ON qry_RandomIDs.QUESTION_ID = tbl_QUESTIONS.QUESTION_ID SET tbl_QUESTIONS.DATE_LAST_USED = Date();




0
 

Author Comment

by:Joe0
ID: 8169139
Thank you very much!!!
I had managed to find a way by making a temporary table, but this works much better!
0
 
LVL 18

Expert Comment

by:1William
ID: 8169154
Plus, you will not suffer exxcesive database bloat from temp tables.  Congrats!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question