How Do Perform An Update Query From One Table To Another?
Apr 4, 2006
Hi there.
Does anyone know how to use an update query to copy some records from one table into another?
I have table1 which has all the contact details and what I would like to do is work out a process for importing records from an excel spreadsheet.
What I have so far is as follows:
1. In access go into the queries tab and select update query
2. Open both table1 and table2
3. Drag the fields to be updated (table1)
4. In Update to select [table2].[table2 column name]
When i run this nothing appears to happen. 0 records are appended.
Does anyone now how to go around this with another solution or should I be using other SQL methods?
OK, usually I can figure these things out for myself, but I'm realy stuck on this one and any help would be appreciated
background: I work in a small scale pilot production line, and we take a series of measurements for every part that we make, each of which has a unique ID. Sometimes we need to take the measurements more than once, so that the data output from the measurement system (which it can append straight to an access table) has a part ID and a measurement number, the combination of which is unique (for reasons that I won't go into it is not possible to combine them into a single field, otherwise we would do this)
problem: The measurement system can be temperamental and sometimes sends the same record more than once, and It is horribly time consuming to go through and check manually It occured to me that we could send the measurements to table1 (where they would be stored temporarily), and write a sub to go through each record in table1 in turn, compare the part ID and measurement number to the records stored in table2 (where they would be stored permanently), and append the record to table2 only if the part ID and measurement number combo has not already been taken, before moving onto the next record in table1 and doing the same thing
The part I just can't figure out is how to go through the records in turn and compare them, I can do the append and deletion using SQL, that's not a problem.
if anyone can point in the right direction I'd be realy grateful (or tell me if I'm barking up the wrong tree completely)
How can I edit a query in SQL view to run multiple lines of SQL?
For example I can use the GUI to create a query, then in SQL view enter the following:
DELETE FROM Employees_copy;
which clears the Employees_copy table. Perfect. But what if I want to do this for mulitple tables in the same query eg for a second table Employees_copy_2 as well? I've tried
DELETE FROM Employees_copy DELETE FROM Employees_copy_2;
which gives a syntax error, as does
DELETE FROM Employees_copy, DELETE FROM Employees_copy_2;
I have a table (ANSWERS) that has the following fields: CASE_ID, E1, E2, E3, E4. The ANSWERS table is based on the CASE_ID's in the OBLIGATION TABLE and the CASE_ID field is the only field populated.
Then I have two other tables: OBLIGATIONS table and WAGE table and each has only one field, CASE_ID.
The WAGE table has only CASE_ID's where there is a wage assignment in place.
What I want to do is populate the E1 field in the ANSWERS table with YES for all the CASE_ID'S in the OBLIGATIONS table that that match the CASE_ID's in the WAGE table. Can someone tell me or show me how to do this?
Either I'm stupid or this is hard to explain...sorry.
I have what I think is a very simple solution but I can't figure it out..
I have a table that has jobdescription and currentbadgetype as two of the fields. I want to do this:
if jobdescription=ASD 1 then currentbadgetype=Civilian Badge...
table name is ID_DATA.. I have backed up my data but I don't know how to go about writing an sql statement or creating an update query. I would appreciate any help...
How to update a table from a query. I have a database that tracks overtime, if an employee calls in sick, he is unable to work overtime for 2 weeks. I have a freeze table that tracks the employee, the date they are frozen, to the date they are available to work again.
I would like a query that when ran, will look at the Available to work Again field and if the date they can work again matches todays date, it then edit the employee table turning the freeze field from Frozen (Yes), to Unfrozen (No).
I am using two table
tblemployee and
tblFrozenOvertime
the tblemployee has the overtime Frozen field
and the tblFrozenOvertime table had the Available to work again field which is also on a frmFrozenOvertime.
I have a query that does calculations in the expression builder field. Is it possible to update/populate a field in a table with the calculated field/answer from the query? Thank you.
I have a query that does calculations in the expression builder field. Is it possible to update/populate a field in a table with the calculated field/answer from the query? Thank you.
Alright, i'm almost sure this isn't the best way of doing this,
But i wanna build a scoring system out of three criteria. cost, delivery, and qaulity. I have those tables built along with a contact table where there over all score will be tallied up. Each contact can have more than one entry in the criteria.
So what I did was i built a query to make a new table for each contact to generate the score and the contact id, then using that ID, i update it to the contact table using the UPDATE function...however wheni have 3 criteria, and 400 contacts..this obviously becomes painfully slow.
The question is, is there a way to directly pull the sql query results (summing the total of each contacts score for each criteria), then storing it into the contact table without making a new table in the process?
I would like to run a query and then be able to edit that information. I want the data I change to be put into the table the info was gathered from. Right now when I edit the query data it only saves it there, not on the other table. I tried to do the copy to table but the original table was going to be erased and then written over. What should I do? thanks
Could someone please try to give me a step by step method for using an append query to update a table. Gary gave me a ton of help before but im still a little stuck. Thanks in advance. :)
I have a table with two fields: ID and Date. The ID is set to autoincrement. How can I update a table with the next ID and date to current, each time a query is run.
I have 2 tables that hold similar data. In one table tblMultiSchedule I have a few blank columns. I want to update each column(ActualCost*) with the correct data which is stored in tblOrdersItems. the corresponding fields in each tbl are
I have created the tables I need for my Access Web App.I would like to create queries.I cannot update fields that show in the query (fields have a grey frame and cannot be edited) . I have even created a completely new app with only 1 table (no relationships whatsoever in case this may influence). It is still not possible to edit any field in a query (that would update the underlying table). Are there any general settings in Access I need to enable? Is there a setting in Sharepoint I need to enable?
Okay I have two tables in a query.tblMain and tblStatestblStates has a control number and a state name.tblMain has the same control number as well as a bunch of customer info.The query is simply tblMain.* and then the state name from tblStates.It pulls the information correctly when I link the control number. However, I cannot update the information.If it just has the tblMain table in the query then I can update everything. As soon as I add the tblStates I can't update anything, even if I don't link the two together. Can someone help me figure out what is going on?The reason I have to have a seperate table is that this is for pulling a contracted state which can be different than the customers state, and the only way it is listed in our system is by number so it has to use this number as a reference and then pull up the state name.
i need to be able to use "lookup", i have a set of postcodes from 3000 customers, i need to extract these and compare them to lists i have of postcodes, which make up a region. And run queries from these
e.g a customer on my database has the postcode BD21 7KK
On my list of postcodes i have on paper - WEST YORKSHIRE - BD21 7
therefore The BD21 7KK Customer falls in the West Yorkshire region.
I need to do this four 4 regions with lists i have on paper then run queries.
Can some one please provide the neccessary steps?
Or even give me an example made?
Im really stuck on this at the moment and me job depends on it ! :(
I created an update query that says it has successfully updated 600 records, but when you go into the table, it has not been updated. I am working with an .mde. Does that make a difference?
I first created a select query to find everyone in TX. Then I changed it to an update query to change all TX to CA. Since it did not update the table, I tried running the update query again and this time it says there are not records to update (since it uppposedly updated my 600 records).
This is quite a basic question and I'm sure that it has been posted here some time ago, although I couldn't find it on using the search.
I want to update a field in one table "FieldA" using an update query. I want the update to be based on matching Field B in the updated table, and Field C in a separate table (both tables have the same design structure).
That is, I'd like my query to search each record in the Destination Table to see if its Field B matches a Field C in a separate Table. If there is a match, I'd like to Update Field A to the corresponding field A of the reference table.
I have tried to do this with an Update Query based on both Tables in design view. Thus far, it hasn't yielded any results.
Can someone please post the solution asap. Thanks so much for your help.
Can you tell me if there is a way to add to this query to enter "NO" in the E1 field on the ANSWERS table for those CASE_ID'S where there is no CASE_ID between the OBLIGATION and WAGE tables. In other words if there is a match insert YES and if there is not a match insert NO.
Thanks
UPDATE ANSWERS SET ANSWERS.E1=YES WHERE ANSWERS.CASE_ID IN (SELECT OBLIGATIONS.CASE_ID FROM OBLIGATIONS) AND ANSWERS.CASE_ID IN (SELECT WAGE.CASE_ID FROM WAGE)
I have two tables and one of them (table1) has two fields called pct_id and pct_name. table2 has the field pct_id.
I also created a new field in table2 called pct_name. What i'd like to do is set table2.pct_name=table1.pct_name wherever table1.pct_id=table2.pct_id automatically.
Is there a query that can check to see if its the same and update table2?
Heres what i tried but it didnt work
UPDATE table1 SET table1.pct_name=table2.pct_name WHERE ((table1.pct_id)=(table2.pct_id));
