I posted this on the General board not even realizing there was a board strictly for queries. Maybe someone can help me out.

I have an update query that I am using to bring values from one table to another and it is working fine. However, the problem I have is that it brings all the values over from the table every time I run it.

I have one table that is generating new records every minute or so and then I want to use the update query to add those records to my other table.

Example:

I have PanelID 1-10 in table 1. I run the update query and 1-10 are added to table 2. I also have a Time field in table 2 that is populated by the user. Panel 11-30 are now added to table 1, but when I run the update query again, 1-30 are added to table 2 and the focus is back on the first record of table 2. Everything is right and 1-10 have a time stamp, but 11-30 are now in the table and the focus is back on record 1 when I need it on 10.

What I want to do is just add the new records from table 1 into table 2 because I want the record pointer to stay on the last record which was given a time stamp.

How can I do this?

It is simple to just move the pointer to record 10, but this is will be used in a manufacturing setting, so I am trying to make it where the user has the least amount of button clicks and steps to go through.

Thanks.

You need to have a way to distinguish already-copied records from new records. Perhaps a [DateEntered] field, or even [Copied] as a Yes/No field. When you run your query, use this field as a criteria for selecting the records you want to move.
You don't have to put something in Update To: for every field in an Update Query.

Post back if you need further help,
David R

David,

I think I understand what you are saying, but I need some clarification.

Does the copied field go into table 2? Then each record that I have populated the fields for, I can set the [Copied] field = Yes.

So, I can run the update query and set the Criteria field to Copied = No and then it will bring only the new records over.

I am not sure what I wrote makes sense, but am I following your logic.

Your [Copied] field will go in the one you're pulling FROM. It tells Access "these are not the records you're looking for." Old Jedi Mind trick. http://www.access-programmers.co.uk/ubb/biggrin.gif

You'll probably need to have two update queries, one to pull data over to the new table, the other to go back to your original table and check the [Copied] field.

I'm a little unclear on the why-and-wherefore of your actions, but that should get you rolling.

Good luck,
David R

David,

I am having trouble figuring out how to go back to my other table and filling in the copied field.

What would I do in an update query to go back and perform this action?

This sounded fairly easy, but I must be doing something wrong.

Both queries must have the exact same criteria sections for the exact same fields, in order to affect the same records. One will be an Append Query (sorry for being unclear earlier), to copy the records to your new/master table. The other will be an Update Query whose sole function is to change [Copied] to -1 (Yes).

Is that better? My apologies for not being precise before.

David R

Is every PanelID value unique?

If so, in design view for table2, set the corresponding field's index value to "Yes (No duplicates)", creating a unique key. This will cause Access to only allow 1 record in table2 per PanelID.

When you do the next append query, Access will detect 'key violations' and only append records which aren't already there.

You will get an error message telling you how many records couldn't be appended. If your users find this intimidating, you can disable confirmation warnings just before the append query is run, and restore them just after in code with something like:

DoCmd.SetWarnings False
.
.
Your code
.
.
DoCmd.SetWarnings True

Be careful with leaving messages disabled, because once messages are disabled, you can do a lot of damage to your records with no warning!

OK. This is what I finally ended up doing.

I created a find unmatched query first. Then I used that query in an append query and sent the unmatched records to my second table.

It works great but it is not the way any of you suggested so I don't know if it is the best solution. I guess though if it is not broke, don't fix it.

Thanks for all of your help because it really got me to dive into working with queries. Also, if you see any potential problems with my solution, please let me know.

That's actually a pretty straightforward answer. Good job!

David R

I now have a different problem. I have a form based on my queries. I run the queries and then I requery the form and the focus is still moving to the first record in the form.

The queries are only bringing over the new records which is good, but I need to have the record pointer left on the last record that was populated with a time stamp.

Is there a way to code to tell the application to go to the first record it sees where my copied field = 0.

Example:
DoCmd.GoToRecord Where copied = 0.

This is crucial because I can't have this function go back to the first record because it would change the time stamp in my time field.

Thanks again.

Store the PK of the record you're in, then tell Access to go back to that record with your WHERE clause after running the queries?

Not sure if I'm understanding your question. Post back or start a new topic if need be, since we've sort of ranged afield of the original question.

HTH,
David R