?
Solved

Append query

Posted on 2003-03-18
15
Medium Priority
?
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

771 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