Hi there all,
I've been using sql for a while now alongside MS Enterpise Manager but have recently been given a few tasks to carry out using access. The time i've got to do my work is quite tight so if you could point me in the direction of some of some good on-line tutorials I would be most appreciative.
However the real reason i'm posting is to ask if there is any way you can automate the importing of data from a text file. I basically need this text file loaded into an access database on a weekly basis, is this possible without user interaction? I fear not.
I am trying to automatically import student data from excel into an access relational database structure to use the data to report progress in an ongoing manner.I have managed to import an excel sheet with the raw data and I analysed it through the wizard and have produced a clean relational database with the data.
I was wondering, now that I have the access database structure defined, is there a way to now import new data from another excel file (new data with same headers) to the newly created relational database? I was hoping to append to the existing data with only new data from the excel sheet.
I have an excel file with Student names and what units they are enrolled in. I also have fields where results are shown with the date. So the data looks like:
Joe Bloggs Unit1 PP 1-01-2013 Joe Bloggs Unit2 PP 1-01-2013 Joe Bloggs Unit3 PP 1-01-2013
I have attached a picture showing the structure of the relational database that works.
I need to import 15 csv files into access every month. I have location of all those files listed in a table in the same access DB but i'm not sure how to incorporate them in a macro I have written to import one of those files:
Function Macro_TransferCsvFileTo_RatesTradeData() On Error GoTo Macro_TransferCsvFileTo_RatesData_Err DoCmd.TransferText acImportDelim, "Import_Spec_tbl_RatesData", "tbl_RatesData", "c:Documents and SettingsaggauraDesktopWIPXMLDB_JOH_FI_2011123 0.csv", True, ""
[Code] ....
where c:Documents and SettingsaggauraDesktopWIPXMLDB_JOH_FI_2011123 0.csv is the file name I want to be replaced by each of the file listed by me in the other access table.
I have a table called Cars and a table called Rent
In Cars i have Car ID (Autonumber and primary Key) and Price Per Day
In Rent I have Rent ID, Car ID and Price Per day
The Car ID in Rent is a lookup from the Cars table, but is it possible to make it so that when i enter the Car ID in the Rent table, it automatically fills in the Price per day field for me?
Sorry if something like this has already been posted, i didnt know what this is called so didnt know what to look for.
I'm sure this must be fairly straight forward but I've been going around in circles trying to solve it in VBA without success.
I want the value of a field (Field C) on a form to be automatically entered depending on the value of two other fields (Fields A & B).
e.g. If Field A is Null and Field B is Null then Field C is Null If Field A is not Null and Field B is Null then Field C = "YES" If Field A is not Null and Field B is not Null then Field C = "NO"
I was wondering if there was a way to automatically transfer data from one table into another. Specifically, if I have two tables, one called Students and one Alumni, is there a way I can click a button or something to transfer a student's data file from the Students' table into the Alumni table? Without having to delete the student's file from Student and retyping it all into Alumni?
Also, is there a way to, for instance, type individual monetary amounts into a single entry in a table and have the total automatically summed? Like, if I have a table for Donors, and every time one person makes a donation, add that one donation to their personal information and have another field that gives the total amount of all of their donations?
I have back-end on LINUX, is there any possibilities to automatic export data to *xml? e.g. every 5 min. Maybe some code in vba which can be connected to system timer?
Hi all, Please bear with me if I'm asking a dumb question. I've been tasked to create a database from scratch and I've not had much experience working with Access.
**My Question** Using a form is it possible to have the form automatically enter a "value" in one field based on the information inputed by someone in a different field. ie. When some one puts in a value between 0 and 20 in one field it automatically enters a 1 in the other, and when some one puts in a value between 21 and 30 you get a 2 and so on down the line.
I am working on an attendance file. I have created one MS access form and one excel sheet. On MS Access sheet I have Student ID, Date and Value. ON excel sheet I have Course, Student ID, NSN No and date.
I want data to be automatically appear on excel sheet from ms access form and after updating it or after transferring data i want these fields again empty and ready for next entry.
For example, If I type value .5 in ms access under 01-Jan-2014 for Student ID 1200001, value .5 should automatically appear in cell in ms excel sheet infront of same id (1200001) and under same date (01-jan-2014).
I have a database that I import data from an excel spreadsheet into multiple times daily. The table that this data is imported into has several key fields that if the data already exisits in the table, and I attempt to import data that is the same except for one or more of the key fields is different. At this time the database it creates a different record. I am trying to get the database to overwrite the data in the database.
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
Hi, i am a new comer. i have a table for students' comment for the first semester. however, in the 2nd semester i want to replace them with import data from excel to this table, but on the same student there are 2 rows of records (1 is 1st semester and 1 is 2nd semester).
how can i replace the new data to the old data? or how can i delete all the old data in the exsiting table so that i can import a new excel file?
I'm trying to import some data from an excel sheet to a table in my database. The import wizard won't allow me to import the data into an existing table, only into a new one which is a nuisance. The data formats are the same. Any idea's what might me wrong Please.
I'm importing data from a spreadsheet to a new table in access. After the import, there are 10 empty records added at the end of the table. I think this has happened before but I don't remember why. Any ideas? Thanks!:confused:
I want to import data into a table from a csv file. The table is failry lengthy but to try this out I have the following:
tblTest - the table I want the data to end up in tblTestcsv - the intermediate table I empty then put the csv data into test.csv - the csv file (obviously)
I have used somde code which gets the data into the tblTestcsv OK but then doesn't move it into the tblTest - I get an error each time.
so i need to keep 2 DBs with the same forms (But different data) for the purpose of single/double data entry. i have to constantly import forms from DB1 to DB2 because of the form changes, how do i keep the existing data and import it?
when i try to use the import wizard, it always imports the tables as duplicates. so here is the scenario if it helps to explain better:
1: import FORMS/TABLE structure only from DB1 to DB2 2: DB2 is EMPTY, no DATA, just forms/table copied from DB1 3: enter some data into DB2 4: DB1 forms is updated, so I have to re-import the forms to DB2 5: new DB2 is again EMPTY. how do I re-import JUST the DATA portion from previous DB2 version into new DB2?
hope this makes sense. please provide a step-by-step if possible. like what fields to check and how to use the import wizard. thanks very much!
Hi Im having a real problem. Im trying to import a query into Excel so a fancy graph can be made. In Excel when I go to Data-> Import External Data then connect to my Database. A list of queries and tables appear however the query I want is not in the list but it definately exists in Access why is this?? Really Frustrating
I created a database to assist in analyzing the contents of an Excel spreadsheet (provided by a third-party contract). The format of the data in the spreadsheet is denormalized to look something like the following:
It's important to note that the ChildIDs are NOT unique.
This was not a problem initially because I was able to import the entire worksheet into a single table and then run queries to populate the Parent and Child tables.
HOWEVER, they are now introducing "versioning" in a way that is causing a problem for me. When a new version is being created, they repeat the Parent and ALL of the Child rows, but only put the versioning information on the FIRST Child in the group. (They refuse to repeat the versioning information on all of the rows). So the data now looks like the following:
I MUST relate all of the Child rows for a particular version to the correct version of the Parent, but have not been able to come up with any idea of how to do so.
Does anyone have suggestions on how to approach this problem?
I need some advice on what is the best way to import data. I have a access 2003 database that tracks converter boxes that my company sends out for repair. Currently they have a excel file that is imported into the tables which establishes the converters that are out being fixed. When they come back from service they enter the date manually from the packing slip by searching for the converter in the forms search. We now are able to get the received converters on a excel spreadsheet. Is there a way to import the date from the excel file to the access table. Any advice would be great.
I accepted a month long job in putting together an Access database for a trucking company. I don't know how to make the incoming data know that it is related to its perspective field in other tables.Also, I am not understanding is how to link the tables. I have a main table with all the data. It is by date, and has all transactions, which Access assigned a number per transaction as the primary key. Then I have an Employee table (the truck drivers), a Customer table (the truck driver's company), an employee productivity table (includes wages, pay rate, Emp names, etc) and a truck table.
I tried linking the tables, but not sure if it is correct. I linked the Customer and Employee table via an EmpID foreign key in the Customer table. The employee productivity table is linked to the employee table via an EmpProd foreign key in the employee table. And the Customer table is linked to the truck table via a Cust foreign key in the Truck table. The table with all data is indexed to the Emp table, and under relationships the arrow points from the Emp table to the all data table, but there is no one to many (etc.) relationship because I did not know how to establish one. I tried reversing the arrow, but it wouldn't let me. I don’t even know the difference between an indexed link verses a relationship link.
The data itself is not linked. The Employees in the employee table does not know that it is the same field in the other tables. The all data table has EmpName daily info with only about 20 names, but about 4,000 fields. I listed the 20 names in the EmpName field of the tblEmp, but do not understand how to make both tables realize that it is the same field. It does not seem to realize that it is the same field because it does not rearrange the two field data with the correct data. I have this problem with all my tables.
So when I need to add the rest of the data, I don’t think the data knows where to go.
What am I doing wrong?
(I am using Access 2007 at work, and completed a class on 2010.)
My query is really to do with the best way to add new records to their database.
The database has four tables:
[Species] which is linked to multiple loci in the [Locus] table. For each Locus their are multiple alleles in the [Allele] table. Additionally, there is the [Reference] table, each reference can be linked to one or more loci in [Locus].
Users could add a new species, it's loci, the alleles for each locus and select the reference for each locus from a list (or add a new reference) using the Form used to view the data. However, this is very time-consuming when there are a large number of species to add but is easiest for me, as it requires little or no extra coding.
The other way I was thinking, would be to do a kind of batch update from an excel file. This would suit the users better as their data are already in this format.
The problem is that I guess I can't do a simple import spreadsheet due to the one-to-many relationships, as there would be no primary/foriegn keys in the excel sheet.
Would the best way around this be to add the first species, then for this species add the first locus and its alleles, then the next locus and so on.. then the next species? This way I could use the keys as they are generated..
Alternatively, I could get excel to generate the keys, and query the Access database to make sure it is not generating keys already in use. Then I can do a more simple import procedure...
I can do either using VB. Which do you think would be 'best'? Or should I just tell the users they'll have to enter stuff by hand the long way?!
I am attempting to import a .csv file into Microsoft Access. The .csv file is a raw data export from an online application used within the business.
One of the fields reported on within the file is unformatted, ie. "free-text". Some users are entering a comma within this "free-text" field, which is throwing my import table out of alignment.
Is there some way that I can continue to import the .csv file, but ignore any commas that appear within this one field.
Hi, I need some help with the following problem. Thanks in advance!
I have imported an Excel spreadsheet into Access database. I will need to constantly add new entries to the tables. I need to connect all my tables together, the main table is called "Data". From looking at Access examples, I believer there should be many other table IDs inside this table (so that they all get a relationship). I imported 5 tables, and each one is dependent of each other. I do not know how to connect the tables together because I'm importing all the data, instead of creating new tables and putting the table IDs into one main table to start off with. I know if I start off without needing to import any data, this will not be difficult to do. The main "Data" has about 30 fields, and the user will only need to type in 15 fields and the rest of the fields can be looked up from the other four tables. So I'm not sure how I can make the connection between all the tables, plus when I need to add new entries to one of the tables, how do I connect the tables so that all of them will be connected? I've tried using queries and having queries within queries and this takes forever to load. If there is a way that I can limit the use of queries or just use tables to do the job that would be great. Thanks again!
