Yeah the "Key" field has the indexed setting set to "Yes (Duplicates OK)"
So I guess I didn't understand the nature of the NOT IN command.
Sounds like I would need to export the required records to a temp table and run it on that then insert them back again after the query finishes.
Is there a more efficient way to do it than that?
Shal
Shal
Last Activity: Feb 19 '08, 03:40 AM
Joined: Sep 10 '07
Location:
-
Fairly simple query taking a long time to process
Hi all,
I have a table with 300,000 records in it and I want to run an update that has a few clauses in the WHERE command and just changes one boolean value on a subset of records. What I am curious about is the query, (if it works at all) takes ages to complete. I suppose 300,000 records is alot to process and I will sometimes want to change this boolean value on up to 20,000 of those, but I have had times of over an hour and it is... -
Other than the error message coming up everything is now fine. I also can't find anything actually being deleted so I am running with it for now.
Thanks again for the helpLeave a comment:
-
Now I am getting somewhere, thanks for that.
I had to re-write my query in the module because it was just a
SELECT * FROM XXXX
type one before, but with the formatting in it I am pretty close to done now. The other thing I think I was missing is that the Export Specification needed to be applied to the query not the table.
I am getting an error telling me XX records will be deleted each time I run it...Leave a comment:
-
I created a dummy extra field in the Database in case this is what was suggested and re set up the Export Specification. I then checked if I could set the start and end of fields to split up what is getting exported for the time reading but could not.Leave a comment:
-
Thanks again, seemed like an easy solution but I must be doing something wrong. When I went into the export specification and added a new field so that I could chop the time into date and a time sections, trying to save that spec or hit ok brought up the error message :
"The number of fields in your export specification does not match the number of fields in the table you have chosen to export."
Even so, when...Leave a comment:
-
Thanks for the reply.
I tried to move the lines separating the text fields. They moved fine, the reason it doesn't help me is that you can move the right side of a field and adjust the field's width but not the left. If you move the left line of the field it narrows the field to the left of the line not to the right. Hence I can't chop off the start of the text in a particular field and can't remove the added in Date reference to a...Leave a comment:
-
I now have set the date field to be only 10 characters in the Export Specification and this chops off the added on time for the date field. So the date information in the text file is now usable.
I can't do this for the time fields though because the Date gets added on before the time is printed and you can't tell the Export specification to only start from somewhere after the beginning of that field. I also ask for the time in Short...Leave a comment:
-
I was able to fix this in the most part by defining the dates as text in the Export Specification while leaving them as Short Date etc in their attributes in the table in Access.
My only issue now is that the last 2 spaces of my date field are being cut off in the text file such as below:
339581 20/03/20 9:51:00 20/03/20 9:52:00
434106 27/03/20 3:11:00 27/03/20 3:11:00
434107 30/03/20 4:31:44...Leave a comment:
-
I have the same issue.
I run a script that is simply dumping a table into a text file for another user to pick up. The attributes of the date's fields are set to "Short Date" and they display only the date in Access. The issue comes when the text file is viewed, all the rest of the data is fine but the Date has had a time added. The time fields have also had a date from year 1899 added. This is an issue for me as I need to export...Leave a comment:
No activity results to display
Leave a comment: