Update Queries Question
Dec 14, 2007
Hi all -
I'm going to reveal my actual lack of knowledge here but I am needing help again. I need to update a temp table with information from a select query. But, for the life of me I can't seem to get it set right so that it will update.
The one caveat that I have (at least one, there may be more I am not thinking of) is that there is no ID number that is associated with the product descriptions in the temp table. In fact, it is a combination of 3 fields that make up a unique record - it is PartDescName, PartType, and PartObjName.
The design of most of this stuff is not my doing, but I have to live within this design as I am only one of many developers working on this and I don't have much say in how some of this goes off.
So, anyway, if anyone has some suggestions for me as to how to be able to use an update query to update the fields in a different table, that would be great. The only way I've found so far is to iterate through as a recordset and place the values in the appropriate column when all three identification columns match the three in the other table. This is takes quite a long time for it to do and would be not customer friendly when trying to view a report based on this data.
View Replies
ADVERTISEMENT
Mar 30, 2006
I am trying to stop access displaying the "You are about Update 1 Record" etc message when i run an update query. I know i can do this in Tools/Options screen but the problem is that the database is going to be used by multiple users, and rather than changing each persons Action query option I was wondering whether there is something i can put in to the code Globally to halt the message.
Any help would be appreciated.
Regards
Mark
View 2 Replies
View Related
Aug 7, 2007
This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:
View 3 Replies
View Related
Nov 18, 2014
I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?
I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.
View 2 Replies
View Related
Dec 14, 2005
do update queries work on linked tables ??
i am getting a message saying operation must use an updateable query
View 7 Replies
View Related
Jul 16, 2007
I'm trying to update a table from a summation query.
It keep bombing saying not an updatable query???
It works if I I have the summation query append a table then
update one table from another.
BUT!!!
I need to do this several time so... is there a way to update a table from a summation query?
Thanks for your help,
View 4 Replies
View Related
Sep 19, 2005
Just wondering if someone can point me in the right direction so that i can solve my problem?
Basically what i have is a select query that carries out some calculations based on data entered. These calculations are expressions as i am sure you guys know. what i want to do is put the value from the expression/calculation into my table in the correct fields made for these values. However i have tried everything i can think of to get this data into the tables fields but to no avail.
example. Expr1: [field1]*[field2] the answer created by [Expr1] is the value i want to be placed in [field3]
Can anyone help me on how i can do this
View 4 Replies
View Related
Jan 12, 2006
Happy New Year Everybody
Can Anyone help me with this please
I have a Employee Table with the following columns
EmpID
Name
Address
Telephone
PostCode
Spending
What I want to do is to update the spending column, for eg. I have the following data in spending
1233
1555
1665
I want to set the last 2 digits of the figure to '00'?
Can anybody tell me how to do this please!! I am desparate for it!
THanks
Si
View 4 Replies
View Related
Jan 12, 2006
Happy New Year Everybody
Can Anyone help me with this please
I have a Employee Table with the following columns
EmpID
Name
Address
Telephone
PostCode
Spending
What I want to do is to update the spending column, for eg. I have the following data in spending
1233
1555
1665
I want to set the last 2 digits of the figure to '00'?
Can anybody tell me how to do this please!! I am desparate for it!
THanks
Si
View 3 Replies
View Related
Jun 8, 2006
I have created 6 update queries that take data from 6 different tables (created using crosstab queries) and place it into a single table. I ran each query after i had created it. The first worked fine. However, the next five simply updated the specified fields with the same data from the first update query!
The only explanation i could think of for this was that maybe the data was being cached as a result of the way i created the queries. I created each update query by editing and renaming the previous one. Was this a big mistake?!
View 2 Replies
View Related
May 6, 2013
I'm looking for a way to fill out 5 records in one row in a table at once. I want to update a supplier and price per unit for supplier via a form. I have price per unit 1-5 and the price will be the same for all 5 records. Is there a way to do this through append or update query and if so which is best in my situation?
View 12 Replies
View Related
Aug 16, 2014
I am facing problem regarding updating of certain field of table. For example i would like to take data from a column of Table1 and would like insert that data into particular column in Table2.
Suppose Table One has data:
Table 1
ID Name amount
1 a 0
34 b 0
3 ab 0
Table2
ID Name amount
34 a 400
1 b 900
3 ab 4500
There are about 2000 records in each table.I would like to update column "Amount" of Table1 by taking data from Table2 "Amount" Field. how can i update the amount column by keeping the ID field?
View 5 Replies
View Related
Sep 15, 2006
Hi,Not sure where to post this as it covers both queries, forms and partly tables.ISSUE:I need to add two records to a query with one record in a form. Is this possible?BACKGROUND:This is a database with TV programs and the times they are to be broadcast. Tables:One table. "table_programs" contains the information about each program (title, description, length etc.). The other table, "table_times" has the times for the programs (date, start_time etc.), where one program can be shown multiple times.Query:There is one query, "query_programtimes", that combines the above two tables + two calculation fields. One calculates the end time of a program by adding the start time (from the times table) and the length (from the programs table). The other calculates a file name, based on the weekday of the date field.Form:The form is based on the "table_programs" table, with the "query_programtimes" as the data source for a subform. There I enter a program and in the sub-form one or more times for it to be broadcast.So far so good. The form adds one record to the query for each time that a program is broadcast.Now what I'd like to do is to add another record in the query for each scheduled time. That is for entering a file that will be played when a program is finished. That is, it'll take its title from the file name that is calculated in the query and the scheduled programs end time as its start time.Alternatively, and perhaps even better, this data could be in its own query and then a third query can combine the two to get the full schedule.Is there any way to do this? That is, one entry in the form is supposed to update two queries at the same time.Hope I made myself clear... :)Thanks!
View 1 Replies
View Related
Jan 23, 2008
I have set up a table (A) that is linked to another table(B) in my database. This linked table (A) is then used in a number of queries. When the data in the original table (A) is updated it does update the data in the linked table (B) as it should. However, when I run the queries they do not bring up any records. It seems that they are not looking at an updated version of the linked table (A) because if I rebuild the query it finds the records as it should. Any advice on this would be very gratefully received.
View 1 Replies
View Related
Mar 16, 2007
Hi,
Is it possible to update multiple rows in a Table at one time using Update Query?
Ta
Kasey
View 5 Replies
View Related
Mar 12, 2007
I have a table with a field called "description"
another field in fame table called "length of cable"
In description field it contains among other things, the length in it . It may be at various locations in the test data in the field. The length may be in the form xft or xxft xx foot xxfeet xx feet..
I have another field that I need to put the length in a standard form like xx feet
(The field will have different units in it later not just feet.)
I am using update query
with
criteria of Like "*3ft*" Or Like "*3 foot*" Or Like "*3 ft*" Or Like "*3 foot"
with update to (of the length of cable field ) set to 3 Feet
The data has many lengths
1ft
2ft
3ft
4ft
5ft
6ft
7ft
10ft
12ft
14ft
15ft
20ft
25ft
and one....
I would like to automate it to one query and hopefully avoid the problem I have when doing it as is, *5ft* also finds 25ft and 15ft incorrectly for the goal of the update. same on 12ft 14ft
' and " are the symbols for inches and feet.
Also some of the data is in the form xx' or xx" I hit a wall on this due to the ' character
need to do the same to that as well.
Any suggestions '''
before I lose any more hair ...
View 6 Replies
View Related
Dec 27, 2013
I am new to MS Access and using MS Access 2013.I just added 50 new fields into a table. I need to update these new fields on every record.The table layout is as followed:
Table1
-Company Id
-Question1
-Question2
-Question50
[code]....
View 6 Replies
View Related
Dec 18, 2014
im working on an update query (written in VB coding) for one of my tables, for now im just setting the value to 1 (as currently all the data is set to 0, so for now it works), however id like to improve upon the query and get it to add 1 every time instead of setting it to 1 (i can work with it the way it is, but changing it would give me more info)
currently its at
DoCmd.RunSQL "UPDATE tblBatches SET tblBatches.batchCarryover = 1;"
View 1 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
Jun 21, 2013
Have a table (tblDailyResults) with 4 fields (ID,TestName,Result,TestDate). It contains a snapshot of about 1,000 records. All fields have data except "Result", which is null. I also have a table (tblResults) with 3 fields (ID,TestName,Result), it contains about 100,000 Records of historical results. What I need to do is Update the "Result" field in "tblDailyResults" with a randomly select value from tblResults where the two testname match.
View 1 Replies
View Related
Apr 14, 2014
I have the following fields
[safetycheckdue] is a date field
[safetycheckfreq] is a numeric field
i need an update query to update the field [safetycheckdue] with the value in [safetycheckfreq]
when i run the query, it just shows the existing value with adding the numbers of days to the safetycheckdue date.
View 1 Replies
View Related
Feb 14, 2014
possible to have an update query to only update 20 rows with a new data in the fields? And, how do I pass the information that I want to update only data from a certain date.If I have the word Cat and want to update it to Dog, but only with the dates 12-01-2014, where do I need to put the date part (also possible to do this for dates that are greater than 12-01-2014).
View 8 Replies
View Related
Aug 10, 2015
I have a query which reads a table, calculates some values and updates some of the table's fields. The issue that I have is that sometime (and randomly) the update skips one record. The query uses an inner join and looks as follows:
Code:
UPDATE TEMP4 AS t1 INNER JOIN TEMP4 AS t2
ON (t1.Month = DATEADD(""m"",1,t2.Month))
SET t1.index=(1+t1.ratio)* t2.index, t1.ratio = IIF( t2.index=0 ,t2.ratio, t2.index)
I can run the query multiple times without seeing any problem. When the issue occurs I do not receive any error.
I should also add that the query/update is run via VBA code. Not sure if this makes any difference.
Why this happen and how I can force the query to always update all records?
View 9 Replies
View Related
Oct 14, 2014
one of my tables.
tbl_type1
-type1_id
-PONumber
tbl_type2
-type2_id
-PONumber
tbl_bill
-billID
-PONumber
-bill_type
Now I have huge data from tbl_bill which contains mix PONumber depends on type. If bill_type is type1 then it will get data from tbl_type1 table and vise versa. the problem is tbl_type1 and tbl_type2 is changing, when someone update this table then the data in tbl_bill will not match to 2 tables.
Now, i need an update query where I can update all records of tbl_bill to match with tbl_type1 and tbl_type2 PONumber.
View 3 Replies
View Related
Aug 26, 2014
I have an SQL table that uses bigint on the primary key field. When I lilnk this table into Access 2010, this table is not even visible because bigint isn't recognized by Access.I have a field on this SQL table that I need to update and I was looking at using a Pass Through query in Access. I have a query in Access that creates a list of records that I need to update.
View 3 Replies
View Related
Apr 4, 2014
I am not sure if my coding is efficient or not but it takes so long to read lets say tbCOMPANY 40k records and find if values of two columns exists in tbRESULT which holds 30k records. Then if it doesn't find any records in the tbRESULT it will just insert the row otherwise an update will be executed.
What I did was:
1. Read tbCOMPANY each row using for loop
2. tbCOMPANY.FindFirst to lookup if values exists in the tbRESULT
3. If tbCOMPANY.noMatch, it will insert into tbRESULT
4. else it will update the matched row in the tbRESULT
Its been taking like over 30 minutes to process this and still ongoing and it will still keep on running. Now Access is showing as Not Responding in the task manager.., Any efficient way to do this?
View 3 Replies
View Related
