Editing Data In Multiple Fields In Ms Access
Oct 4, 2007
Hi,I have a table which contains sales information like deal name,closing date of deal,Monthly revenue information,total revenue yearly also quarterwise
information.The below table gives some idea....
i want to enter data in multiple fileds ex:when I enter revenue amount it should get updated in corresponding month based on the closing date information.Suppose closing date for a particular deal is mar-08 then the total revenue entered in revenue field should get updated in march month field.Please help me how to edit data in multiple field and also logic to apply to solve my problem.I have to design a report based on this information and source should be one table so i want to edit data in main table.
Any help would be appreciated...
I am new to ms access database so got stuck..pls help me
Closing date Jan Feb ar Apr May Jun Revenue Q1 Q2 Q3 Q4
Mar-08 1,500 1,500 375 0 0 0
Jun-08 4,000 4,000 0 0 0 0
Jun-08 6,000 6,000 0 4,500 0 0
Jun-08 3,000 3,000 0 0 0 0
Jun-08 1,720 1,720 0 0 0 0
Jun-08 20,000 20,000 0 0 0 0
Jun-08 2,000 2,000 0 0 0 0
Jun-08 20,000 20,000 0 0 0 0
Thanks in advance. yuor help is appreciated,
Regards,
kala
View Replies
ADVERTISEMENT
Dec 10, 2014
Create form to search multiple fields in table
Return records that match search
Open the record that you want in Form View for editing
View 1 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
Jul 6, 2006
Hey guys,
Does anyone have any ideas as to how you might go about editing multiple records at once? So maybe have a listbox with checkboxes, so you can select items in your DB, then, using a form, edit/set one field for all the selected items. Any ideas?
View 4 Replies
View Related
Feb 22, 2005
I have a form with a lookup, which when a product code is entered, the description automatically appears.
This is fine 99% of the time, but occasionally an unusual product needs to be added or the description slightly changed.
Is there a way around this problem, as when using the lookup, this field is then locked and cannot be edited at all
Thanks
Chris
View 3 Replies
View Related
Aug 25, 2012
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this:
Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
Any other ways to import the data?
View 1 Replies
View Related
Feb 18, 2015
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
View 5 Replies
View Related
May 1, 2013
I have produced a table, the records from which have to be selected by individuals for auditing purposes.
I created a second table with individuals initials and passwords. This is linked by the initials to the main table. Records on the main table are returned when the query is run.
At first I could not edit fields in the query but changed the recordset type to Dynaset (inconsistent). I am now able to edit the query fields. However, having created a form based on the query, I am unable to edit the fields in the form. This is the basis that the auditors will use to score so is vital. Have I missed any other switches or techniques?
View 6 Replies
View Related
Dec 20, 2013
I have attached a portion of my database. if you go into the form called frmtest, select a branch from the very top drop down, select a detail and click the Edit button.how to have the information populate all the fields for editing. The Cost and Quantity fields work but my combo box fields don't properly work. The top combo box populates but when the record is updated it doesn't save the id. The second combo box doesn't even put the data in the data box.The main table where IDs and fields are is the BRANCH_EXP table, it's a linking table which links tables with many to many realationships.
View 3 Replies
View Related
Jul 25, 2013
I have tables that are ODBC linked to a SQL database. The source tables have record date and time stamps written into one field. This means that I can't use the Access date filter functions on these fields. Is there an easy way to parse this field into two fields, one each for the date and time?
View 3 Replies
View Related
Mar 4, 2015
I total novice at VBA. I am trying to code a button to modify (the last) record in a subform list and then add a new record based on values in unrelated or unbound fields on the button form.
The following code is based on the first of two YouTube tutorials (this bit on the edit) and looks like it should work. Except that my Access 2010 with Visual Basic for Aplication v7 does not recognise the type definition Database or Recordset
Code:
Private Sub ANOwner_Click()
Dim cn As Integer
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
[Code] .....
View 6 Replies
View Related
Mar 4, 2008
Hello All,
I'm trying to limit the data entered into a specifc field, but also the data must be unique with respect to other fields.
i.e.
Two fields: System A & System B.
Data entered into System A, can not be entered into System B.
Anyway to prevent this from recurring?
Would I use a validation rule?
Thanks in advance
View 4 Replies
View Related
Jun 8, 2006
Hi All
Been handed a contacts database, one field of the DB includes all the industry sectors associated with the customer, with multiple industry references separated only by a comma within the field (e.g. Insurance-Life,Insurance-Broking,Insurance-Motor,Insurance-Pensions,Insurance-Home,Insurance-Health) – I want to split this one field into multiple fields using the comma as the break point (this is something that’s relative easy to do in Excel with the ‘Text to Columns’ tool – but unfortunately it can’t take the 130,000 odd records of the databasel)
Can any one recommend an expression or method that will see the 6 industry references split into 6 separate fields ?
Thanks
Jim
View 1 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
Mar 11, 2014
I imported a big table from excel with many columns. Access when I brought it in determined that they should be "text" format. I don't want to sit and change each field to a "number-double". Is there a way to quickly change data type for multiple fields at once?
View 1 Replies
View Related
Sep 25, 2013
I am trying to create the form so when the first field (Start Date) has a date selected, it limits the following field (Report Date) to the matching available data, and same with the third field (Production Division) on the previous two.
I also need the form to allow new dates / production division combinations, but not duplicate.
I starting working with a cascade function but got confused and not sure if that is where I should be headed.
I have attached 2 different formats of the same sample data.
View 13 Replies
View Related
Sep 3, 2013
I have an access table that looks like the below:
"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1~field2~field3~field4~field5~field6~fie ld7"
"field1", "field2, field3, field4, field5, field6, field7"
"field1~field2~field3~field4~field5~field6~fie ld7"
I've got a module which loads the data into a table, but it can't handle the records that are ~ delimited, so spits them out as a single field.
I know there is some VBA code that can be used to split comma delimited records, and I've seen bits of it floating around online and tried to alter it to work for the ~, but I get the feeling that what I've seen is only a part of the required code, how to put it all together to make something that actually works.
So, what I need to do is;
- Split some records in a table out into multiple fields where there is a ~ present
- Place these newly split fields into a table (I don't mind if it ends up in a new table or not)
View 7 Replies
View Related
Mar 25, 2013
I have a Table that looks like the following:
A B C D E
10 12 8 4 14
9 10 7 5 12
12 13 10 3 17
10 11 6 6 15
I have figured out how to calculate the regression statistics for A vs B on a query. So it looks like this...
Predicted B Difference between
A B m b via regression Predicted and Actual
10 12 .9474 1.7895 11.263 -.737
9 10 .9474 1.7895 10.316 .316
12 13 .9474 1.7895 13.158 .158
10 11 .9474 1.7895 11.263 .263
Question: What I am looking to do is to duplicate this for A vs C, A vs D, A vs E, B vs C, B vs D, and so on until all of the pairs are calculated and put into one table/query. I know I can just create more fields to the right with the A vs C data and continue on that way, but this does not seem very efficient and I am limited in the number of fields that can be created. So it seems that "going down" rather than across makes more sense however I do not know how to move on to the next pair (A vs C and so on).
I thought of running the regression query over and over again and "recording" the data generated into a table. Once again I have the same problem. How do I tell the regression query to run the next pair? If my data is setup incorrectly, I am willing to change that as well.
View 1 Replies
View Related
Nov 7, 2004
I have created a table with multiple Yes/No fields. I wish to create a
query where the user can select from a form that displays all the Yes/No fields
Is it possible for this to be done
Thanks for your help
REFINEMENT OF PROBLEM
I have created a table for a small library at work. The items in the table are item_number, author, title etc. Also I wanted to classify each item into various categories. In the Table I have set the categories as Yes/No fields. An item can belong to a number of categories eg admin, finance,resources etc. What I would like to do is for a user to be able to interegrate the table via a query to find the articles that meet the criteria they want to search on. Ideally this would be done via a form that the user can tick the applicable categories (Yes/No fields). I tried setting up as parameter queries however this is very messy. Is there a way it can be done. Sorry for confusion in first posting.
View 4 Replies
View Related
Feb 19, 2014
"Dynamically search multiple fields" using my own customer data.
I'd like to add in additional searches as what he has will let you do one search but I'd like to, in his example, search on vintage and state, or the like. I assume Id need two multi-searches for this.
Right now the data is in excel so I have a completely blank page to fill.
View 2 Replies
View Related
Feb 19, 2013
Let's say I have a table called "Courses" which among other things has "Completed"(Yes/No) and "Category"(Text).
Now, there also another table "Progress" which has similar columns.
I want Progress.Completed to reflect if all courses of the specified category have been completed.
How would I do that? I'm new to access ...
View 1 Replies
View Related
May 30, 2015
I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:
One table, two fields
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"
When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.
View 9 Replies
View Related
Mar 12, 2013
Access 2010
I have a form bound to a table which has 20 (name)fields and 1 date per record. A user fills in this form first to indicate which people are present this day. I know it would be better to use 1 name per record but that would in this case not work since the form must show all names for that day before saving and closing.
I have a different form where I use 4 combo boxes and a date field. The values that can be selected in the combo boxes should come from the 20 names on the first form and with the same date as the other form. So only 4 people of the 20 indicated as present on that day can be selected to have performed some task.
I know how to select with a combo box from different records, but how would I do it from different fields in the same record? And then also for a particular date?
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
Oct 19, 2006
I want to make a field where data can be enterred but once its saved cannot be deleted or changed in any way.
Anyone have any ideas how this can be done?:confused:
View 10 Replies
View Related
Jan 6, 2014
I had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.
The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.
View 2 Replies
View Related
