I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet. They have a common row ie say account number and other common fields that need to be updated.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click() 'Creates quote date and prints quote Me.QuoteDate = Now() Me.cbAgentID.Requery DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
I have a database with existing data, that is not normalized, :eek: and all the data is in one field... This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1: C10A CHOLEST&TRIGLY ATORVASTATIN LIPITOR PFIZER SIMVASTATIN SIMVASTATIN TEVA TEVA M1A ANTIRHEUMATIC N-STEROID ETODOLAC ETOPAN TARO PHARMA Thanks so much for your help... :confused:
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code Table: Table 1 Update To: [Table 2].[Access Bar Code]
Field: Access Box Number Table: Table 1 Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
I have two tables, one is a list of customer details including email addresses and the other a list of customers and email addresses that have unsubscribed from my mailing list. How can I use the unsubcribe table to update a field in the details table so I know who's unsubscribed?
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.
Hello everybody. I am new to Access and as such I don’t know how to perform the following. Please help me out.
The project is about a library loan system. I have a database consisting of the following tables: member (MemCode, LName, …), book (BkCode, Title, TotalCopies,…), purchase (PurDate, BkCode, CopiesBought,…) and transaction (MemCode, BkCode, RentalDate,ReturnDate,Returned).
TotalCopies is the total no. of copies there are of the book CopiesBought is the number of copies that have been purchased RentalDate is the date on which book is being loaned ReturnDate is the last date on which the book has to be returned Returned is a Yes/No field to indicate if the book has been returned
In the library a member can take only one book at a time.
Problem 1: The library may have several copies of a particular book, but all will have the same code. For example, a book, let’s say Harry Potter 6 has code 97. Initially only 1 copy of the book is purchased. When the librarian sees the book has great demand, he decides to purchase 3 more copies. All the copies will have the same code, i.e., 97. Only the TotalCopies will become 4.
BkCode is Autonumber in book table and Number in Purchase table. Relationship is 1:many.
I have a Purchase form to record details of books being purchased. I have a command button “Book form” that opens the book form when the user clicks on it. He will use it if the book being purchased is not already available in the library. What I want is:
(i)If he opens the book form to fill in details of the book, then when he closes the form I want the book details to automatically appear in the corresponding fields on the Purchase form. (ii)The CopiesBought will have to be used to update the TotalCopies in the book table. How should I do this?
Problem 2: When the user wants to record a loan, the system will need to check if that member already has a book which he has not yet returned. If it is so, then the system will need to provide a prompt to inform the user of this and consequently blocks the user from completing the transaction. How do I do this?
I've been working on this problme for several weeks, thinking about it for the last month or so, and am at a loss of what to try next. I'm working on a database for my office that tracks all of the permits we have issued. Every year our permit fees increase by the CPI (consumer price index) and the permit fee entry in the table needs to be updated (75 to 80 entries).
Rather than typing them in every year (too many chances for errors) I'd like to be able to update our fee schedule table (8 entries, look up table) and have that table update all of our fee entries in the permit table.
Any ideas of how to go about this? Thank you for your help.
I have a data entry form where country(ies) (there can be more than one for each project) is/are entered in a sub-form on a tab control. Countries not listed are entered by the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer) Dim strSQL As String Dim strMsg As String Dim ctl As Control Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?" If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then strSQL = "INSERT INTO tblCOUNTRY (CountryName) " strSQL = strSQL & "VALUES('" & NewData & "');" CurrentDb.Execute strSQL Response = acDataErrAdded Else ctl.Undo Response = acDataErrContinue End If End Sub
This works fine. However, if I restrict the country names by a region selection on the main form (for example if region Africa is selected on the the African countries are preselected) the NotIn List does not work. Any way to work around this?
Another question, I want the user to select a region (a combo box ) on the main form and when a new country (using the NotInList event) is entered in the subform both values go into the same record in tblCountry (which has two fields CountryName and Region). For example, I enter AFRICA for region on the main form and on the sub-form I enter Malawi which is not listed. I have tried the following:
Private Sub CountryName_NotInList(NewData As String, Response As Integer) Dim strSQL As String Dim strMsg As String Dim ctl As Control Set ctl = Screen.ActiveControl
strMsg = "Country " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?" If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then strSQL = "INSERT INTO tblCOUNTRY (CountryName, Region) " strSQL = strSQL & "VALUES('" & NewData & "', FORMS!frmAddPro!Region);" CurrentDb.Execute strSQL Response = acDataErrAdded Else ctl.Undo Response = acDataErrContinue End If End Sub
But it does not work (Region is the control on the main form named frmAddPro). Any suggestions would be welcome. Thanks. Niels
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded 121/2/201310:00:00 AM3:00:00 PM 221/3/20132:00:00 AM11:00:00 AM 321/4/201312:15:00 AM11:30:00 AM 421/5/201310:25:00 PM11:00:00 AM 531/2/201311:00:00 AM3:30:00 PM 631/3/201312:00:00 PM10:00:00 PM 731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
I need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.
The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.
I have two tables, Table Products & Table Sold and I'm trying to aggregate the products table to reduce the total number of products and I want this update to happen with all tables that share a 1 to many relationship with the product table. (table sold is one of those).I have products apple, pear, bananna. I am now aggregating them to all just be called fruit. Problem is when I make this change in the product table I get this error:
"The changes you requested to the table were no successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."how to go about aggregating data in a table that has 1 to many relationships with many other tables. A
Hi all I have been nutting out this problem but have been unable to find a solution, even my learned colleague is at a loss to help. This is an data update query using combo boxes and forms. I have 2 databases, Data and App, I have linked 2 tables, Main and Supervisor from the Data.mdb to the App.mdb. Supervisor has 2 fields ID and Name. Main has multiple fields but is linked to Supervisor by the ID field. I have a query that gets details from the Main Table and this is entered into a form. I deleted the SupervisorID text box and inserted a combo box using the wizard, it gets its data from a query that gets details from the Supervisor table showing the Supervisor name, the ID field in the dropdown is hidden. The combo box selection is held in the SupervisorID of the Main table. What I want to do is change the Supervisor name using the combo box however I am unable to select another name from the dropdown list. I have tried changing the Data Entry property of the form to Yes, this did not work. Allow edits is set to yes. I have tried adding another combo box which gets the data straight from the Supervisor table but I have the same problem. Can anyone help, we think it is a simple property setting but all we have tried has failed to date. Thanks in advance. Craig
I have a VBA script that looks at a date on a form, adds a certain number of months (selected by the user). at the moment i can output the new data to a message box. I want to update a table with the new data value.
I have an Access db in a 3 person multi-user environment on a Windows network.
There is a "PRODUCTION" db and a "DEVELOPMENT" db.
Let's call them PROD and DEV.
PROD has the most current data, shipping records, item master, customer data, sales, etc. - but not the most current structure.
DEV has the most current struture - all the front end stuff - forms, functions, modules, etc. - but not the most current data.
The way I've handled this in the past (it seemed to work) was to take the PROD db and rename it to PRODX. Then take the DEV db and rename it PROD. Then open DEV (now called PROD) using the usual shortcut to PROD.
So now that opens fine, and I have to update all the tables from PRODX - and I mean ALL of them - since I don't really know what data has changed since the last update.If I try to delete records I get blocked by access because of all the related records (I don't have cascade delete set on every relationship). So I delete the whole table - ALL of them (this requires me to also delete the relationships). Then I IMPORT all the tables from PRODX (these have the current data). But now the relationships are all gone. It seemed at first that the relationships were back and intact - but when I last looked they were gone. So this is my problem.
HOW do I COMPLETELY empty ALL of the tables - or even delete them - and then restore them or repopulate them from an exact copy of the db but with current table data - and WITHOUT affecting the relationships?
Obviously any back end structural changes have to be handled differently. Usually by manually making the same change on the PROD db that I had in the DEV db - because deleting the table will cause the structural changes to be lost.
One last thing - I've been working this way on an un-split db, and now I'm in the process of splitting it - which SHOULD make updates much easier.
I am working with MS Access. The database has 2 tables.
-Parent and Student and ParentID is the Primary key as a parent may have multiple Students -There is a form that lets me add students for a particular parent
One of the fields in the Parent Table is FeeDue. I added a field in the Parent Table called NumOfStudents..What I want to do is as follows: When a student is added on the Student Form, I want fee to be calculated automatically for display AND update the FeeDue field in Parent table. Event handler executed when a student is added (checkbox clicked)
OnClick() { Read NumOfStudents from Parent Table
if(student_added = true) NumOfStudents++ else /* This is to cover student withdrawl*/ NumOfStudents--
if(NUmOfStudents = 1) Fee= 400 else Fee = 500 }
student_added check box is on the student form NumOfStudents and FeeDue are fields in Parent
I have a MS Access table containing Dependent social security numbers but some of the dependents social security numbers are blank.
I have a MS Sql Database that contains a table with most of the missing MS Access table dependents Social Security numbers.
How can I take the Dependents social security number from MS Sql Database and copy/update the MS Access table.
I tried the sql code below created from MS Access and it matches 453 records out of 460 dependent social security numbers but how do I update qryGHIAccuracyFile_MissingDepSSN.SSN with the social security number from dbo_depfile1.dep_depend_ssn
Code:
SELECT dbo_depfile1.dep_depend_ssn, qryGHIAccuracyFile_MissingDepSSN.SSN FROM qryGHIAccuracyFile_MissingDepSSN LEFT JOIN dbo_depfile1 ON qryGHIAccuracyFile_MissingDepSSN.MemberSSN = dbo_depfile1.dep_ss_nbr WHERE (((dbo_depfile1.dep_first)=[FirstName]) AND ((dbo_depfile1.dep_last)=[LastName]));
Updating MS Access table dependent blank SSNs with SSN in MS Sql Database.
i am getting stuck while updating the data in the database table using a command button in the MS Access2003 forms. when i click the command button in the form, a message "Run time 2185: you cant refer to a property or method for a control unless the control has a focus". the code is as follows.
rivate Sub Command10_Click() Dim query As String
query = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"
If (cn.State <> 1) Then cn.Open "dsn=ABC", "", "" End If rs.Open query, cn, adOpenKeyset, adLockOptimistic RESOURCEINFO.SetFocus rs.Fields(0) = RESOURCEINFO rs.Update
rs.Close Set rs = Nothing cn.Close Set cn = Nothing On Error GoTo Err_Command10_Click
Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.
Code: Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer) Dim rst As DAO.Recordset 'Update value list with user input. On Error GoTo ErrHandler Dim bytUpdate As Byte
[Code] ....
It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
