Multiple Update To Same Field
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 Replies
ADVERTISEMENT
Jan 12, 2007
Hello,
Was wondering if there is a way without building individual update queries, to update info in one field that has multiple criteria ?
Basically I need to change/update daily multiple ID numbers to new ID numbers, long story on why this needs to be done but for now I need to do it this way.
Example: 12345 update to ABCDE, 6789 update to FGHI, etc. These ID's are all within the same field in the table.
It works fine running each ID one at a time but was wondering if it is possible to do all these updates within one query or code ?
Thanks for any assistance
View 6 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
Apr 21, 2013
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
Can I do this using only update query?
View 2 Replies
View Related
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
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, 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 6 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
Feb 12, 2015
I have a main form and a subform.
Both forms have the field called JobID in common.
Both forms have a field called JobStatus.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
View 3 Replies
View Related
Apr 7, 2006
I would like to create a text box where I can input todays date, then I want to be able to select the tables I want to update with that information.
So if I input todays date I can then update my Stock, Sales and Control tables with todays date. Tomorrow I would only like to update the Control and Tape tables with it as its the weekend.
I been playing around with 'new date for next record' scenario but the problem is that on the weekend we would not input any data into some of the tables.
Hope I've explained myself clear enough.
Thanks
Tim
View 3 Replies
View Related
Oct 26, 2005
I am developing an access db where employees are allowed to load tools from a tool store.
I have three tables: tblEmployee, tblTool, tblOnLoan.
tblTool includes a field "QtyOnHand" which is the quantity of a particular tool in store available to be loaned.
tblOnLoan is used to record which employee has what tools on loan.
When an employee loans a tool i need to be able to reduce the QtyOnHand of the tool and record the loan details in tblOnLoan.
When the employee returns the tool i need to increas the QtyOnHand of the tool and record the return against the original loan in tblOnLoan.
i have not yet been able to work out how to reduce or increase the QtyOnHand as tools are loaned or returned.
Can anyone please help?
View 1 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
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
Aug 11, 2005
Hoping that this is a simple problem :confused:
I have an orders form and subform, ie one order can have several product records with the following firlds
Product
Ordered
Received
At the bottom I have a command button 'ReceiveOrder'
What I want is on click of this button that the received field (that is 0 by default) will equal Ordered.
ie If I had orderd 2 and I hit the button it would change the recieved to 2 as well.
This much I have done with the following code
Private Sub ReceiveButton_Click()
Me.frmReceivingSubform.Form!QtyReceived = Me.frmReceivingSubform.Form!QtyOrdered
End Sub
This work fine if there is only one product, but if I have more than one it will only update the selected record, is there an ammendment I can do that means all the product records on that particular order will update.
I've searched the forum for the answers but can't find the answer, can anyone help,
Thanks
View 6 Replies
View Related
Aug 30, 2005
hi, i've a form with 2 combo boxes (month and year) and 2 buttons "save" and "get". I'm trying to figure out how to use that button to perform 4 tasks.
task 1:
when i select the month and the year from the combo boxes and i click the button "get", the form should populate the number of checkboxes equavalent to the number of days of that month. e.g: if i select feb 2008, i should have 29 checkboxes (due to leap year) or i select apr 2005, i should have 30 checkboxes.
assuming that i've done the date validation function.. like jan, mar, may, jul, aug, oct and dec should return 31 days, leap year etc.
task 2:
then how should i assign the date value to the checkbox? say if i check on the 5 th box, it should return me a value of 05/09/2005 (assuming i select sept in my month combo box and 2005 in my year combo box earlier on)
task 3:
how should i code the label for my checkboxes? i wan to have 2 labels on top of the checkbox. the first label to show the name of the day (e.g: mon, tue etc) and the second label to show the day (e.g: 1, 2, 3)
task 4:
not sure if this task is a tall order.. but nonetheless, i need to consult the experts here. let's say when i check multiple checkboxes at one go, how to update into the table i want?
e,g: i check 9 boxes (e.g: 01/09/05, 03/09/2005, 04/09/2005... 23/09/2005) then when i click the button "save", i should have nine records in my table designated to save the data.
pls help. thanks. :)
View 11 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
Sep 29, 2014
I have an update sql statement that isn't quite working properly.
My where clause has 3 criteria.
*print = -1
*stDocCriteria (project_num and client_id)
Below is what the code looks like.
Code:
updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print = -1 and " & stDocCriteria
Add watch: so you guys can see "stDocCriteria"
Code:
updateSQL = "update tblTimedTasks set printed =-1, invoice_date = Now() where print =-1 and ([project_num]= '140012' And [client_id] = 87)"
I want to only updates records that meet all three criteria. The above sql not only updates all records that satisfy stDocCriteria regardless if print is -1(true) or 0(false).
View 10 Replies
View Related
May 19, 2013
how to update multiple row in one command button e.g. cmdsave.Example as below: Table Name = Master
PRNUMBER PONUMBER QTY
123456 RLP120001 12
123457 RLP120001 45
123458 RLP120002 23
How to save both RLP120001 with status "OK" because normal form only can have one form with one data. Then, I come out with filter form which can view PO number RLP120001 but how to update both PO Number using filter form. In my filter form, save button only save above row which pr 123456.
View 1 Replies
View Related
Sep 11, 2007
I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.
The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.
Query (GetTxnVolAmtTR"):
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id
FROM ft_payees AS p, ft_txn_summary AS t
WHERE p.payee_id=t.payee_id And p.market=t.market
GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc
SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "")
WHERE rc.component_name='Total Revenue';
as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!
View 3 Replies
View Related
Apr 17, 2015
I have two forms, both of which have a field called JobID (Form A is the primary Key and Form B is the linked field - Relationship: One to Many).
Form A = FrmJobs
Form B = FrmPurchaseOrders
I have put a command button on Form A. When I click the button I need it to open Form B and then automatically fill the field JobID in Form B with the same value in Form A from which it was opened. I have tried the following:
Private Sub Command214_Click()
Me.Refresh
On Error GoTo Err_Command214_Click
Dim stDocName As String
Dim stLinkCriteria As String
[code]...
The above code works on my old database but not on my new database. I get the following message "Object does not support this property or method".
View 6 Replies
View Related
Apr 25, 2013
I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.
Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.
My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.
View 6 Replies
View Related
Mar 28, 2007
tblAppointment will take register appointments.
tblTreatment contains Cost for different AppointmentTypes.
The above two tables are linked as 1-M relationship (1 Treatment can have Many Appointments)
Entering data in the tblAppointment - AppointmentType and AppointmentCost are the fields in tblAppointment. AppointmentType a drop down box, so that a specific AppointmentType can be selected.
Problem: Now depending on the AppointmentType selected the AppointmentCost field should automatically display the correct Cost.
Can anyone help please?
Thanks
Paz
View 2 Replies
View Related
