Good Afternoon
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.
Thanks
VC
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.
View 1 Replies View RelatedI 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?
thx
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:
View 1 Replies View RelatedI 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.
Dim sSql As String
DoCmd.TransferText acImportDelim, , "tblTestcsv", sFileLocation, False
sSql = "INSERT INTO tblTest (forename,surname,phone) " & _
"SELECT F2,F3,F4" & _
"FROM tblTestcsv"
CurrentDb.Execute sSql
Any help as to waht I am doing wrong would be gratefully received.
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
Cheers :confused: :confused: :confused:
bikeboardsurf
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:
Parent1ID Parent1Name Child1ID Child1Name
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name
Parent2ID Parent2Name Child1ID Child1Name
Parent2ID Parent2Name Child4ID Child4Name
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:
Parent1ID Parent1Name Child1ID Child1Name EffectiveDate1
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name
Parent1ID Parent1Name Child1ID Child1Name EffectiveDate2
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name
Parent1ID Parent1Name Child5ID Child5Name
Parent2ID Parent2Name Child1ID Child1Name EffectiveDate1
Parent2ID Parent2Name Child4ID Child4Name
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.
Gregg
I have 2 tables that contain the exact same columns.
Basically titles and singers of CD's I have
I want to make one table out of them.
Is there a way to do this?
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.
Cheers for any help
Pete
Hi all
How do i import all data tables from another database using code?
Thanks
Damon
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.)
Hi There,
I'm building a database for a friend's company.
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?!
Cheers
Hi,
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.
Any advice would be greatly appreciated.
Thank you.
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!
I have read lots about importing files but can't find anything on how to handle importing data that repeats itself in blocks from with in a txt file.
Note that I intend to run the import as an event each time the db is opened.
the data is an audit log that looks like this:
Date: 12-01-2004 Time: 11:16:57
Transaction #: 061318 Cashier ID: NB
Computer: COUNTER2
Sale Recorded. Transaction Total: 2.5
Date: 12-01-2004 Time: 11:20:55
Transaction #: 061319 Cashier ID: NB
Computer: COUNTER2
Sale Recorded. Transaction Total: 5
Date: 12-01-2004 Time: 11:59:48
Cashier ID: RC
Computer: COUNTER2
Deleted All Lines From Transaction
Date: 12-01-2004 Time: 12:01:17
Cashier ID: RC
Computer: COUNTER2
Opened Cash Drawer--NoSale
Whats the best way of doing this.
Cheers
I want to import a csv file and store the good data (which matches input masks, validation etc) into one table, and all the bad data which is rejected into another table.
I've read a bit about an import error table, and although I do get errors when importing the csv file, no such table is generated.
Many thanks
Cc
I want to import data from one DB to another,
but only data without affecting the table structure and names.
In the import dialog box has only two options,
import tables and data
import tables only.
I want the third one ;)
import data only.
Is there any automatic way or do I have to make a VB sub?
What I'm doing now is to importing tables and data
to the second DB, but as u know this results in having every table
twice like Students Students1 Classes Classes1 and so on.
Then I'm removing one by one all the old tables and then renaming every the
Table1 to Table.
As you can imagine this is not a practical solution and it can be implemented only to small DBs, with a small number of tables.
In my case we are talking about a huge DB with over 20 tables,
and the above process (importing data) has to be done at least once per month.
Let me inform you that copying data and only data from one base to another is the only method suits in my case so please avoid suggesting alternative solutions.
Thanks in advance
I hope that someone out here can help me with this. I have recently had a format change from how I received updated data for my database. I used to update my tables from TXT files. There were several update queries/macros written to import the data from 5 or 6 different TXT files into one table. Well; my source has changed :eek: The downside: I can only get my data from an XLS or CSV file now; but the upside is all the data is only in 1 file.
The macros/queries were written years ago and I've been having migranes trying to figure out how this was all done. So, i've sort of started from scratch. I can easily import the data into a table; I have even gotten our front end of the database working so it's just a click of the mouse again.
The problem I have is that the data that is in this table is linked everywhere throughout this spiderweb of a database! I was unable to import my data into the existing table because that table's field names are different than what I have in the XLS file. I get all sorts of errors when I do this. So I am currently importing into a new table. (I.E. MASS and MASS Imports)
Is there an easy way to update data from table 'MASS Imports' to 'MASS' ?? what I was thinking was to use an update query with both tables and link each line.... Or am I way off base? Is there an easier way to do this?
Thank You!!
Hello All,
I currently have a macro that imports data from a spreadsheet and then a query that adds the data into the main table.
But when I want to import new data it deletes the old data out of the table and inserts new data. How can I adjust the query so that it "updates" the new data into the table instead of deleting and then adding?
Another problem is empty records, is there a way of importing data where field 1 has data?
Any help would be great.
Thanks.
Hi guys,
I´m a unexperienced user of access and it´s my first post in this forum. Hope to make myself clear.
I have several tables. My problem is rather simple but I didn´t find out to solve it. I searched on the forum but didn´t find it. I might not have the appropriate vocabulary yet to perform an efficient search.
My problem is that I have a form to fill a sales table (Salg). For each entry I need to fill the postnumber and the location. On another table (Poststed) is registered the Postnumber and the location name. On the form I did a combobox to get the post numbers. Then to get the location, In another box, which should come automatically after the "Postnr" has been selected, it should display the "location name". I did a "DLOOKUP" for that:
=DLookUp("[Poststed]";"Postnummer";"[Postnr] ='" & Skjemaer!Salg!Postnr & "'")
and it works fine. The name come automatically.
However, when I validate the "post" then all the data in the boxes are register in the destination table, but not the "location nam". It seems it is not bound to the table because the DLOOKUP function is in the source field.
Does anybody knows how I can bound it to the table.
Or if I should use another way to do the task, I´m open to any suggetions.
I hope I made myself clear enough.
Thanks for taking the time reading my post
Frederik
I have a table with a primary key.
This table has address type information in it. I have an import *.csv file that I want to overwrite to this table. I want it to identify the primary key and then update address fields if applicable. I also want it to recognize when no key exists and than create a new record. However, I'm getting an error because the primary key exists and then it doesn't update the other fields. PLEASE HELP ME. How can I overwrite data in a table?
I want to import data from access to excel my excel file has columns say
a b c d e f g h i j k
my access table has columns
b d e f g i k
now when i run transferspreadsheet, since some columns are not there in the access table, it gives a error. how can i import this data?
I want to import an Excel spreadsheet into a table in my Access DB.
I am using following: DoCmd.TransferSpreadsheet acImport, 0, "tblSchedule", "C:mailinimport.xls", false
This code works but it is appending to the table.
What is the best way to update the table - or would it be best to delete the old table and then to create a new one to import tha data into?
If the latter would be most elegant solution, can anyone help with some code that would do this ?
Thanks
Mat