Update Multiple Fields At Once
Sep 6, 2007
Hey guys-
I have 2 identical tables. I want to update the data from Table1 to go into Table2. Each table has well over 70+ fields in them. Instead of handwriting out each [Table].[Field] in either SQL or the Designer- is there a shortcut to tell Access to grab all the fields from Table1 and update all the fields in Table2 (all the fields have the same name)? I just don't have the energy to type it all out- I figure there's got to be a way...
I know when you do an APPEND query in the designer- it will do this for you- but not the UPDATE query...
Thanks!
View Replies
ADVERTISEMENT
Sep 25, 2014
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
View 2 Replies
View Related
Jun 27, 2005
I am trying to setup a database for vehicle stock control.
Im not sure if I have gone about this the right way as I am new to this but thus far it is working correctly except for one annoying problem.
The database consists of so far
tblIAWVehicleDetails (Primary key "IAWvehicleID" autonumber)
tblIAWSellers (Primary key autonumber)
tblIAWBuyers (Primary key autonumber)
tblIAWStates (Primary key autonumber)
tblIAWStatus (Primary key autonumber)
tblIAWSafetyDetails (Primary key autonumber)
tblIAWSold (Primary key autonumber)
In the Vehicle Details table a stock number has to be manually added as this will be used for new stock as well as current stock (Number range from 100 - whatever) "IAWVehicleNo".
This table contains all relevent data with reguards to make, model, bodytype, color etc.
The sellers table contains the details of the seller Name, address, Phone, LicenceNo etc.
The Buyers table contains employee names.
The States table contains all Australian states.
The Status table contains current vehicle status Retail, Wholesale, Wrecking etc.
The Sold table contains the details of the person who purchased the vehicle if sold.
The Safety details table contains a safety checklist for pre purchase inspections eg: Headlights yes/no checkbox, Headlight text field for any extra info.
It also has a field for a safety Certificate No once the vehicle is checked and recieves a Safety cert.
The forms are setup as
frmIAWVehicleDetails
frmIAWSellers Subform
frmIAWVehicleSafetyDetails
frmIAWVehicleSafetyDetails Subform
The Vehicle Details,Sellers,Sold & SafetyDetails tables all have the IAWVehicleID & IAWVehicleNo Fields but when the details are entered through the forms the IAWVehicleNo which is the manually entered number only updates to the tblIAWVehiclesDetails but the other IAWVehicleNo fields in the other tables remain blank.
Any advice or help would be greatly appreciated.
View 6 Replies
View Related
Apr 10, 2008
Access2007- Building a query to handle future input...
I run an update query that does what it is supposed to do and gives the proper values.
THEN I add a one more field to the source with the builder using + that has no data in it yet, the query fails and gives blanks, even though one of the fields in the source has data in it.
I would think that the "no data" field would be considered 0.00 value and be added together to the field that has values giving at least the value of the field with values as a result of the query.
Is there a setting somewhere that I need to change so that the no value and the value would be combined? I am using the builder to combine the values in the query.
Thanks.
View 2 Replies
View Related
Apr 4, 2013
I am designing an update query for a specific field in my table
There are 3 fields: PROVIDER_GROUP, MEDI_CAL_IND, MEMBER_LOW_CAT
I want to update the value of "Y" in the MEDI_CAL_IND field to "N" if:
the medi_cal_ind value is "Y" AND the PROVIDER_GROUP is "Medicare_Part_D"
OR
the medi-cal_ind value is "Y" AND the MEMBER_LOW_CAT is "Commercial"
A screenshot of the table is attached.
View 5 Replies
View Related
Jul 26, 2006
I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...
What is the code to have multiple fields updaterd based on what is input into a field?
---
Example:
A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)
---
I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.
Thanks a million!!!
View 1 Replies
View Related
Nov 6, 2014
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee
Field: Name
Form: frmUpdateEmployees
Queries: 16 that depend on the table and field above.
View 5 Replies
View Related
Aug 12, 2015
How can i update fields of one table with update query where creteria are more than one 1
Example:
DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"
What is the syntax error ...
View 5 Replies
View Related
Oct 28, 2014
I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
View 1 Replies
View Related
Nov 1, 2012
I'm running an update query that's based on a select query (that runs some calculations). The update query is updating ALL the rows that should be updated with the information in the select query with data from the final row in the select query, and not on a per ID basis as I think I have it set-up to do. The data looks correct in the update query, but again it's not coming out right.
Here's the SQL for what I've written so far:
UPDATE [Customer_Data Query], Customer_Data INNER JOIN Baseline ON Customer_Data.ID = Baseline.ID SET Baseline.[Unit Hours] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units], Baseline.Availability = [Customer_Data]![Perceived_Avail], Baseline.[Hours Available] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units]*[Customer_Data]![Perceived_Avail],
[Code] ....
I've also tried to force the update to the proper row by adding a criteria based on ID.
Select query, here it is:
SELECT Customer_Data.ID, Customer_Data.Data_Set_Version, Customer_Data.Number_Units, Customer_Data.Perceived_Avail, Customer_Data.MTTR_MTBF, Customer_Data.MT_TR_OR_BF_Hours, Customer_Data.Utilization, Customer_Data.Percent_Scheduled, Customer_Data.Sched_Percent_of_PM, Customer_Data.Sched_PM_Duration, Customer_Data.Sched_CBM_Duration, Customer_Data.Sched_CBM_From_PM, Customer_Data.React_Detect, Customer_Data.React_Rework, Customer_Data.React_False_Alarms,
[Code] ....
View 3 Replies
View Related
Nov 26, 2007
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
thank you
-Tim
View 2 Replies
View Related
Aug 21, 2013
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
View 6 Replies
View Related
Nov 29, 2006
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies
View Related
Nov 16, 2006
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE,
Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
The CSV data set attached
tHANK YOU
SIMON
View 2 Replies
View Related
Oct 28, 2013
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
View 7 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Jun 27, 2006
I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?
SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount
FROM [JE 06 Log]
WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));
View 6 Replies
View Related
Jul 5, 2005
ok, here is the code i've got...
Code:Dim DAOdb As Database Dim DAOrs As DAO.Recordset Dim sSQL As String Set DAOdb = CurrentDb() 'select the record corresponding with the value in the hidden textbox sSQL = "Select * from table where field =" & "'" & Me.textbox.Value & "'" & ";" Set DAOrs = DAOdb.OpenRecordset(sSQL) With DAOrs .Edit 'update this field in the table .Fields("Status") = "Complete" .Update End With DAOrs.Close DAOdb.Close
i want to be able to update all of the fields that match the textbox. as it is now, it only updates one. how can i loop through all of the records and update all of the ones that meet the criteria??
thanks for your help. It's greatly appreciated!
*j
View 2 Replies
View Related
Sep 6, 2006
Hi, I have created a field called age, where I have a formula that takes the date of birth out of the IDENTITY NUMBER and calculated the peoples age, I have then created a field where I would like to allocate groups depending on their age, i.e, 18-25 = Group1, 26-35 = Group 2,etc... I am having trouble with this, firstly, I cannot get the formulas to work individually and secondly, I get an error that says I have duplicate fields to update.
PLEASE HELP!!!!
View 2 Replies
View Related
Sep 13, 2005
Hello,
I would like to update multiple records in one query.
The scenerio is that i have 3x3 (3 in row and 3 in column) text feilds and i want to update it once, in one query after entering the data in text fileds.
Can anybody help me please......????
Waiting of your quick reply.
View 3 Replies
View Related
Feb 21, 2012
I've got an order detail form which has
product id, product description, customer, quantity and below the form a Total quantity
When I enter the same product id, and when i press the button to update, the table of stock quantity deducts from total quantity
If I use different product ids, it doesn't work, but updates only the current product id, where the mouse current record is set by mouse.
By the way i use dlookup to check if the stockquantity has enough items.
View 10 Replies
View Related
Dec 6, 2011
I need to update data in an Access database(ADO,VBA).
In the Access database, there are three tables as following:
Unit(ID,UName,TID,AID)
Type(TID,TName)
Area(AID,AName)
When a unit has changed its type or area, then the db needs to be updated. My question is how to do it. Can I get a recordset by joining three table, and do the update in one go?
View 8 Replies
View Related
May 1, 2008
I have a one-to-many query with two tables. I'm using the query as a record source for a subform.
The subform displays the correct data I'm after however I can't update any fields on the it due to the underlying query having a one to many relationship. I understand that and thats fine.
Problem is I need users to be able to update one field on this subform as required. Is there a way to allow this while keeping the underlying query the same?
View 1 Replies
View Related
Apr 27, 2005
Is it possible to update 3 fields from one drop down box? the drop down box's sql is as follows:
SELECT [tblCurrentRoute].[KFC ID], [tblCurrentRoute].[PERIOD/MONTH], [tblCurrentRoute].[PK ID] FROM [tblCurrentRoute]
I would like to update the following fields on tblMasterEvaluations:
tblCurrentRoute.KFC ID = tblMasterEvaluations.StoreID
tblCurrentRoute.Period/Month = tblMasterEvaluations.Period
tblCurrentRoute.PK ID = tblMasterEvaluations.PKID
I have the drop down showing the correct information, and it stores the Store ID correctly; however, I would like to use that selection to update other fields in my table also...is this possible? Thanks!
View 6 Replies
View Related
May 10, 2005
Hey guys..
the fields in my form don't update for some odd reason. i tried making another form and it works fine there. But i want these fields to be enabled on the current form because it will be the main menu and i want it to be there already.
the record source is fine, all the properties are fine... but it does not update..
there is a combo box which updates perfectly..
any idea ??
View 3 Replies
View Related
Sep 2, 2005
Hi,
Hope someone can help. What I’m trying to do is have a combo box look up values from one table and add them to another. I have used the combo box wizard to look up a value from table1 and add it to table2 but is there a way that it can lookup 2 values and add them to the second table. For instance if I had to tables like :-
Table1
First Name
Last Name
Table2
First Name
Last Name
Can a combo box lookup and update both fields for example if I have a combo looking up the first name can it also lookup the last name and add them to the corresponding fields in the second table but with the user only selecting the first name in the combo box.
I’m sorry if I don’t make much sense I’m new to access and fairly new to forums
Thanks for your help
View 1 Replies
View Related
