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?
All comments much appreciated
Thanks
View Replies
ADVERTISEMENT
May 30, 2006
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)
regards
Bogzla
View 5 Replies
View Related
Aug 11, 2005
Hi,
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;
any suggestions?
View 2 Replies
View Related
Feb 20, 2015
I'm trying to get a query to perform a calculation and round the results.
The fields that I am running the calculation and am trying to round are Data Type Number, properties Field size Single and decimal places Auto.
I have tried the built in function described in Allen Browne's site to no avail.
[URL]
I have tried rounding the individual fields and then adding result.
I have also tried rounding the result (as attached).
View 11 Replies
View Related
Nov 2, 2006
First off, please be gentle with me, I am a total noob on Access/SQL/etc....
Here's what I'm trying to do.
let's say I have one table with data like this
Class range
Class ValLow ValHigh
----- ---- ----
Class1 0 50
Class2 51 65
Class3 66 77
Class4 78 90
etc.
Data to classify
Entry Val
----- ----
A 10
B 26
C 52
D 85
etc.
I want a query that returns:
Entry Class
----- -----
A Class1
B Class1
C Class2
D Class4
Now I suppose this could be done with an expression with a lot of IIF's, but there are potentially THOUSANDS of classes/ranges in my real data.
I doesn't seem that Access has the concept of a CASE statement, and even if it did, it would be a huge CASE statement.
In Excel this would be trivial using a temporary column and a Vlookup, but I have hundreds of thousands of records.
How can I create a query that will do this classification? I don't even know enough about how to do it to figure out what to google on... :(
View 11 Replies
View Related
Nov 26, 2013
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
View 3 Replies
View Related
Oct 3, 2006
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.
View 4 Replies
View Related
Oct 11, 2006
Hi all..
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...
Thanks again
R~
View 2 Replies
View Related
May 26, 2015
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.
View 6 Replies
View Related
Apr 3, 2006
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.
Jimmy
View 1 Replies
View Related
Apr 3, 2006
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.
Jimmy
View 1 Replies
View Related
Apr 27, 2006
Hi all,
Simple question...I think. Is it possible to update values in a table, based on the results of a query?
For example, I have tblPRR and qryProcessed.
When I run qryProcessed, I would like the field "Status" in 'tblPRR' to update from 'No' to 'Yes'.
Could anyone advise if this is possible?
Cheers
Rob
View 2 Replies
View Related
Jun 9, 2006
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?
View 1 Replies
View Related
Aug 10, 2006
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
View 3 Replies
View Related
May 25, 2007
Problem Solved!!!!!!!!!
View 3 Replies
View Related
Jul 26, 2007
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. :)
View 1 Replies
View Related
Dec 5, 2007
Hello Everyone!
I am new here and this is my first post.
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.
Thanks,
Ket
View 6 Replies
View Related
Jul 2, 2014
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
tblMultiSchedule.OrderItemID1
tblOrdersItems.OrdersItemsID
I've been trying all day to have an update query to input the actual cost data into the tblMultiSchedule.
I know the rules regarding duplicate data but this is the only way i can do this.
View 4 Replies
View Related
Aug 14, 2014
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?
View 1 Replies
View Related
May 2, 2007
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.
View 3 Replies
View Related
Mar 10, 2006
Hi i really need help!
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 ! :(
Thanks
View 1 Replies
View Related
Aug 28, 2007
Dear All,
This is my first post in the forum and would like to apologise for any shortcomings.
I want to update a field in my table with the result from a query.
Eg. Table A with two fields, a1 (recorda1) & a2 (recorda2)
Result from Query (recordq) has to be replaced in Table A, field a2.
after the code is run, (recorda2) should be replaced by (recordq)
Can anyone help me how to do this with the help of an update query.
Regards to all
Basheer
View 4 Replies
View Related
Nov 3, 2005
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).
Your help is appreciated.
View 1 Replies
View Related
Nov 24, 2005
Hi all,
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.
Regards,
Mike J.
View 4 Replies
View Related
Oct 3, 2006
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)
View 1 Replies
View Related
Dec 7, 2006
Hi all,
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));
Thanks in advance
View 1 Replies
View Related
