man i could not figure out why my excel file wasnt appending to my table. until i patiently watched your video and you said you have to spell it right. turns out i misspelled some words and mixed up some things. thank you so much man.
Great video. If your getting "subscript out of range" error each time you try importing from a worksheet to a table -- try the radio button for new table instead. It worked for me. I got that notion about 4 seconds into this great vid after being frustrated all night trying one idea after another. Looking for solutions that would fix the "to a table" import problems was humiliating and antagonizing.
Can I import one spreadsheet into Access, then later, add columns to the table that it makes? AND Can I later make a different table and link data from one column in one table to data in another column in the other table. Can an Access DB grow in this manner? Sorry to be a stranger asking questions. Thanks for the video. I'll start watching your other videos. Thanks again, B.
Hi, i used Access back when i was in college, but at work im getting back into it as it should be easier than excel for our data analysis. I'm struggling to get my monthly data to import to the same table. Each month i will have additional new customers and right now i have customer name as my primary key. During my import from excel into an existing table, Access provides an error message stating # records lost due to key violations. Do you have any suggestions on how to best upload data monthly and what to select for a primary key? Thanks your videos are super great!
Adam, is it possible to have the linking allow updates in both directions? That is if I can update the data in access it is also updated in excel? Thanks!
Great videos, if is possible can you do a series on creating a simple Vehicle Maintenance Access database, to keep track of repairs or maintenance, gasoline usage, etc, I haven't found one in the web or in youtube. Thanks
Hi, My name is Tyrone and Love your videos. At age 60 am deciding to build a database in Access. After building a Table 30 fields am having problem importing pdf files or text files in to my present Table in Access and also will like to update and keep the previous data for records to look at. Thank you
Very nice video tutorial, I've been search this tutorial. thank you so much. Can you make tutorial for Account Receivable with details of customer? tks.
Prof Morgan... the access file I am working with has data in the tables presently and is already set up with the tables tied to reports, queries, and dashboard buttons. I want to copy in data from an excel file into an existing table. Trying to save hours entering one record at a time. So when I paste these records in.. will it compromise those reports , etc.?? Thanks for your time sir.
Hi J Cascio, What I would recommend doing is saving a copy of your database somewhere else on your computer. From there, try to import the information and see what happens. Since it's a copy of the file, it shouldn't cause any problems to the original file. I doubt you'll have issues with the reports, queries, etc. but it's better to be safe than sorry. I hope this helps.
Hi there, really like your videos. I have a bit of problem. I have data I need to mail merge from Excel to Power Point. Is there a way to do that? I need something similar to Word or Publisher mail merge. Thanks.
Thanks for this video, it's really helpful. Though I think i have a problem with my laptop, when ever I click on the External data, it doesn't show saved import, I can only see saved export. This has made it difficult for me to import into my access. I'm using ms access 16. Pls is there anything I'm not doing? Pls help me out here.
Thanks for the toturial , i am trying to import an excel file but it has merged cells so what should I do to complete the task successfully and import it to access
Hi Dyala, Thank you for watching. You will need to unmerge the cells and remove any unnecessary cells from the spreadsheet. I recommend only having column headings and data if possible. I hope this helps.
Another excellent set of videos. Could you make a video on merging two worksheets with similar information? I have two worksheets that contain some identical fields, but other information. I would like to get that information onto one sheet. Thank you.
Hi Mike, Thank you for watching the videos and for the comment, I appreciate it. My recommendation is to export the two tables to Access, then use a Query to get just the data that you want from each sheet. Here is a video of me explaining combining multiple fields into a query: ua-cam.com/video/YWjQcra9dOc/v-deo.html
when importing data from an excel sheet into an access table... will the imported data mess up things when the access table your dumping the data into is linked to other forms and queries within that access file? (sorry to be long winded) Joe C
Hi Joe C, No, the imported table won't link to any other data in your database unless you connect it in the future with a relationship. I hope this helps, thanks for watching.
Hello Professor. I am following step by step and for some reason I am not able to append one Table with another. Is there anyway we can communicate via email or directly ? Thx.
Nice video. But I seek to append just one column of data from Excel into an already existing Access table. I somehow had that field deleted from Access and now I want to import just that column. Is that possible?
Hi, Try adding in a new column (field) in Access and name it the same name as the column in Excel. From there, import the data and it should work. I hope this helps, thanks for watching.
Adam, thank you for your video! It is very helpful. I haven't used ACCESS for over 20 years and needed some brushing up. So thankful i found your videos. However, I am coming across an issue. I am importing my excel file and it has several rows that are duplicated in columns. Such as the SSN and Name. The rest is not duplicated. Then, my file won't import. The error i am getting is: type conversion failure.. do you have a video for such error?
Hi JohnD Great, I recommend combining all of the files on to one specific Excel Workbook. From there, you can import it all from one file. You can choose which columns to include in the Import settings.
Hi Professor, I have a query from an Access database exported to Excel (so i can manipulate the data) and then a link from that excel file back to access. The problem i am having is that the data going back to access doesnt refresh unless i open and close the excel file. I have changed the setting for refresh in excel under the query and the external data menues but it still wont auto refresh. What can i do to accomplish auto refresh without having to manually open excel?
Thanks so much for this video. It was actually a help for me. I was trying to append an excel file into my access table but I was clicking excel under Export instead of under Import & Link.
Hello is it possible to receive datas only from excel? I mean build the DB & Table at Access and handle daily works at Excel then upload excel data to Acess DB weekly or monthly I mean I when I try upload from excel , new table is created at Access while my aim is to upload the data to the table that is already exited in access.
Hi Morgan, I would like to make the price list of parts with 7000 items in access with front page to put the part number only and get all the price and other details in the excel format and then make a list and export to excel, Do let me know we can do
Hi Adam, thank you for this video. I have a question - How shall I Import the excel data from a particular sheet into the access if I have more than 1 sheets in the excel file?
Hi Chetana, Thanks for watching. When you get to the 2nd step of the Import Wizard in Access, you'll see all of the worksheet names listed at the top. Choose the one you want and follow the prompt as needed. I hope this helps!
Heloo. Just want to ask if there's a way to automatic transfer the data from excel to access? Whenever I'll add something on Excel, it will automatically update to my access? Thank you.
This was a really easy and simple video to follow. When I tried it, it worked beautifully. Thanks a lot! BTW, I have an Excel things I'm trying to do, but don't know how to do some of the things I'm trying to accomplish. How can I get in contact with you to see if you might be able to lend me a hand with it, or just give me some pointers?
Hi Dome, Thanks, I'm glad this was helpful for you. The best way to ask me questions is through the comment section on videos. I try to respond to all comments and help out whoever I can. As for learning Excel, I already have a pretty extensive playlist that you can view on my channel, I'll link it below. Just let me know if you have any questions: ua-cam.com/video/tkSkIGrijOY/v-deo.html
Hello professor I have a question how can I prevent a customers list from creating duplicate entries? BTW I want to thank you and let you know I have enjoyed watching and learning from your videos
Hi Enrique, It depends which program you'd like to remove the duplicates from. If you want to remove them in Excel, go to the Data tab, Data Tools group, then select the row you'd like to remove the duplicates from, and click Remove Duplicates. If you want to remove them in Access, make the duplicate customer field the Primary Key. If you do that, Access won't allow duplicates to be made in that field. I hope this is helpful for you. Thank you so much for watching my videos, I am glad you're enjoying them. Have a great weekend!
Hi professor Morgan! Your videos are so helpful and helped me learn a lot, thank you! I had to use Access for the first time at my job, that’s when I discovered your channel and your videos have been extremely informative and easy to follow. If it’s possible, can you make a video about macros? I’m trying to make a button that would automate Importing and exporting data from Access into Excel and vise versa (basically automating data ingestion for both softwares). I know it can be done but I don’t seem to figure out how to do it. If you can’t find the time to make a video about it, can you please explain the process or give me the formula (idk if that’s what it’s called?) for it. It’s kind of urgent and I would really appreciate it. Thanks again :)
Or maybe if it’s troublesome for you, please just direct me to a video or a website or anything that could help me achieve what I’m looking for. Thank you, and waiting for your reply :D
Hi Randa, I'm so glad to hear that my videos have been helpful for you. Access can be pretty intimidating if you've never used it before, so I'm glad you feel more comfortable within the program. Regarding Macros, I do not currently have any Macro videos for MS Access. I found a website that could help you with this, so I will link it below. Hopefully it works for you, so let me know. I may be able to make a video on this in the future, but right now I'm still catching up on other student content. Have a great day and thanks for watching! support.office.com/en-us/article/importexportspreadsheet-macro-action-9973a631-6586-4c1d-9c61-ae167696b750
Hi 888boss88b, I'm not really sure what you mean by template. Please let me know with a little more detail what you're trying to do and I'll do my best to help you out.
Thank you for this easy to follow video. Unfortunately, I still get the error message when I import - The Microsoft Access database engine cannot find the input table or query. Make sure it exists and that name is spelled correctly. How do I fix this? SOS 😅
Is there anyway around importing to a current database from a Excel file that has all characters formatted correctly...but the columns are in a different order? Great vid!
Hi TheParamedics1, I'm sorry but I am a little confused by your question. Typically you can order the fields however you'd like once they're in Access. Please give me a little more information and I will try to help. Thanks for watching the video.
Professor Adam Morgan Wow, thanks for the quick reply! Apologies if my terminology is incorrect, my situation is I have a Access database linked to a website that houses ticket records for that are distributed and worked at my job. These records are currently being mass imported from an Excell table via copy paste from the table to Access by selecting the New empty row below current data in access table. The excel file the table is copied from is formatted accordingly by text and column order or else it will not take the data and cause an error. The raw data that is filtered to create the final report in excel has extraneous data and columns. My current workaround to clean up prior to Access import is a formula to dump the data in a template excell that will automate the correct formating and order for access importing to another sheet in excell. Is there a faster workaround to obtain the correct formating, specificly only taking certain columns needing for import to access? The text is in correct format without an issue, but I need a faster way to format or get access to take the incorrect column order. Apologies for the long post, hard to explain jajaja 😂
No problem, I'll do my best to help here but it's kind of difficult to do over text without seeing the actual document. Linking the data to Excel is not always the best option since if the Excel file is lost, moved, or changed, it can effect the data that it is linked to. I guess the question I have is why not just add the ticket information directly to Access in the first place. If you get it into Access from the start, it will fix the linking issue. I'm not sure how much data is contained in the columns, but if it is not too much to be manually typed, that is always an option. It seems like your current copy and paste method takes time, but works correctly. I'm just wondering what is the point of using both programs as opposed to one or the other. Let me know.
Hello prof! is it possible to import data from excel file which have the same PK with the Access table? so we are like updating the already existing data in Access instead of adding a new records
Hi Abdullah, You're probably getting an error because of the following: 1. The primary key you specified has duplicates, and therefore cannot be a primary key. 2. The primary key you're using is in use in another table in the database. It's a little hard for me to answer this question without seeing your database, but I hope this helps anyway. Have a great day.
so what if you already have data but might have new data in the excel form and you just want to update the access database without duplicating information that was already updated prior, can that be done?
Hi, I hope you can read this. I am trying to import an XLS file but the characters are in Korean. And when I import it to the database, the data is in "????". How do I get MS Access to be able to read Korean characters? I have already tried installing Korean language under MS ACCESS options. Thank you very much
Hi Cedric, I'd recommend translating the information into English before you export the file. I'm not entirely sure that the import will work in other languages. I know Word & Excel are supported in many languages, but I'm not sure how an import would effect that. I hope this helps.
Hey, I have a question, how do you import a spreadsheet with more than one worksheet? Can you please make a video based on it? Or if not please tell me how to do it. Thanks Naeem
Hello Prof. Adam, how do I cross reference my data from excel to my existing database in access? I want to know if my data from excel have the same matches in access. Thank you and great video by the way.
Hi Bruce, I suppose you could convert the Access to Excel, then go to the Data tab and click Remove Duplicates. This should show you all of the examples of duplication. If you see 2 of everything, then they have the same matches from Access. Below is a video on converting Access to Excel. You may find a better solution than this with a web search, but I suppose this way would work. ua-cam.com/video/qXU-nzVPvMs/v-deo.html
Hi Adam, Great tutorial!! I have a table in Access that I took and added more columns to it in Excel. Now I want to link it back with the new columns to Access, what is the best way to do so? Also, I don't have a primary key in excel or access data, so I am afraid that when I link both together, they won't be correctly sorted. Is there a way to fix that? Thank you so much
Hi Ali, Thank you for watching! I would import the Excel table back into Access as a new table, then delete the original. The other option is to add the columns to the original Access table and append the columns, which can be tricky sometimes. If your tables aren't linked with a relationship, I wouldn't worry about deleting the original. You can always resort the table in Access, or create a column in number order in Excel and use that as the primary key. You have some options here, try a few different ways and see which is the best. I hope this helps.
Thank you for you easy to follow Access 2016 videos. Im new to Access 2016 & I need help importing an Excel 2016 spreadsheet whose columns names have changed but the data is mostly same the already existing already existing Access 2016 database except for 1 new field in the excel spreadsheet. There is an access form with a macro button that imports the excel data into once a 2nd macro is click that confirm the data shown after the import button is clicked is correct. Is there a way to mport the excel file into the Access Db then create a macro or update query to change the excel field hearings to match the current AccessDB then just add the one new column? Thanks!
Hi Juanita, Thank you for watching my videos, I appreciate it. You could set up a macro that makes the import steps, but unfortunately, I don't think you can link it to the Access file. I'm not too sure how to solve this problem, and I don't want to direct you to an answer that doesn't work. You could run an update query, and link it to your Excel data I suppose. As long as the file locations do not change, but I haven't tried this myself before.
@@ProfessorAdamMorgan Thank you for the update Professor Adam...Thankfully I was able to do a work around by manually formatting the filenames b4 Iimported them then only copying the data fields not the headers when I import the data. I do have another Access 2016 question...I want to turn on the checkboxes in the Database Tools/Relationships section so that all my tables that have a primary to foreign key /1 to many relationships have the 2 relationship properties checked: Cascade updates & cascade deletes. But both if these options are greyed out on all of my tables with relationship lines. Do you happen to know what I can do to enable both? thanks Juanita
Hi Juanita, I am glad you were able to figure it out. You need to create a one to many relationship to enable both of the cascade options. I made a video on this a while back, have a look at it at the link below. Hopefully it'll help you solve this problem: ua-cam.com/video/NtRAyS0LLlk/v-deo.html
Thank you sir for this. but i have an issue where it stated that field ‘f5’ doesn’t exist in destination. however in my excel and access just consist of four fields . can you help me? tqq
Problem: Example: KeyID is contained in several lists, but some columns are same, some are different. LIST "A" : Customer 1001 Doe | John | 123 Maple Street | Anytown | AZ -- - - - LIST "B" Customer 1001 Doe | John | 333 Pine Street | Anytown | AZ | Purchase date 1/28/19 | Attribute 32 | Attribute 07 ? Would I have to add columns to match so that ALL imported Excel lists are the same? Some Lists have 100 columns, some have 12. If the customer had two different addresses, how would that be handled? You may have video already, I have not found it yet. Thanks!!!!!!
Hi Sam O, If you're trying to append a table, meaning combine Excel data with a current Access table, all columns and field names will have to be exactly the same or it will not work. If the Excel data has 100 columns, you'll need to make sure that all 100 columns are included in the Access table in order to append. If you're trying to send the Excel data to a new table, you can do it by creating a new table within your Access database. If the customers have 2 different addresses, you'll need to create a column for both addresses, rather than having them in separate rows. I hope this helps you out.
Thanks for the info. I have a question: what happens when the existing database has an autonumber column? Do I have to make sure the excel sheet has no over-lapping numbers? Basically, I'm trying to copy a bunch of records, change an aspect of them and reimport them to Access, but I'm worried about the autonumber column. The reimported data should not have the same id numbers. Do I have to adjust them in Excel? Anyway, thanks!
Hi Warwick, The autonumber that is placed on the new Access table is the table's primary key. This means that no number in this column will have a duplicate value. I made a video on primary key's and what they mean and what they do. You shouldn't worry about it too much, it's just to reduce errors in databases. You won't have to change any of your Excel data before the import. Here is a video about primary keys: ua-cam.com/video/7lJS5tklOrE/v-deo.html
Hello Professor Adam; You are my favorite UA-cam teacher that I have found. I would like to know why my Ribbon bar is not active for me to import a workbook from Excel to a table. I click on the External Data icon but the other icons are in color gray. I will apréciate your help.
Brother how can we transfer data from ms access form to ms excel in a new sheet. Means when we fill the form in ms access and click on submit button and the data should be save in ms excel new sheet. Please guide me
Hello, I have a question regarding updating files /adding new data into an existing excel file when there is a connection with pivot tables. Whenever I try to refresh a sheet (when I add new data into the datasheet), my file doesn't want to refresh and says: error, cannot open the source file called "xyz". Is there any possibility to update a file and that the pivot table updates automatically without this error. I don't want manually do every month new dashboards. Thank you in advance!
Hi Ivana, I'm actually not too sure on this one. I would recommend converting the pivot table to a range and seeing if that helps. I'm sorry, but I typically try to make the Excel formatting as basic text as possible before I convert to Access. I hope this helps you, thank you for watching!
Hi Adam, great tutorial which has helped, however I have a linked field in the table I wish to append (linked to another table in the database). When I do as you've described the linked field is empty. Is there any way of importing that information ( I have 12,500 records to enter)
Hi Susan, I'm glad this tutorial was helpful for you. If you're trying to append a table from Excel, verify that the data type in Excel is the same in the original Access table. For example, if you're using currency in Excel as your number format, make sure currency is the field type in Access. I hope this fixes your problem.
As someone who is frantically watching your videos during my timed integrated word/excel/access project, thank you oh Microsoft gods 🙏🏻🙏🏻🙏🏻
you just saved 10 students their lives, thankyou legend 🤍👌👍
You saved many lives today my brother!!! Da wordt ne traktatie op foubert eh moatt 👍
kom mij trakteren a broeder
man i could not figure out why my excel file wasnt appending to my table. until i patiently watched your video and you said you have to spell it right. turns out i misspelled some words and mixed up some things. thank you so much man.
Keep doing all the good works, sir it helps lots of students. Thank you, sir.
Hey thanks Kazi, I'm always happy to help. Thanks for watching and for the comment, have a nice day!
I appreciate the efforts you do to make these concise and apt videos. More to come from you.......Great work
Thanks
Nice job staying on point while explaining in sufficient detail.
This was super helpful. Im taking a course and didn't understand Access and your video was great. Thank you.
Hi Michele,
Great, I'm glad my video was helpful for you. Let me know if you have any other questions in your course.
Great video. If your getting "subscript out of range" error each time you try importing from a worksheet to a table -- try the radio button for new table instead. It worked for me. I got that notion about 4 seconds into this great vid after being frustrated all night trying one idea after another. Looking for solutions that would fix the "to a table" import problems was humiliating and antagonizing.
Hi Greg,
Thank you for posting your problem and solution. I hope you have a nice day.
I am having this same problem, I’ll try that. Thanks
@@merrymeri4700 Good luck. It still works for me.
Hi, Professor Thank you so much for this video! I am having trouble Importing my file, but thanks to your video I think I got it.
No problem Angelina, thank you for watching. I'm glad you were able to import your file, have a nice day!
short and to the point like videos like this should be.
I agree, IsNgUp01. Thanks for watching!
This was extremely useful to convert information from Google Forms to Excel and then to Access. Thanks!
Hi Phychological,
Yeah, luckily those 3 programs all work pretty well together so I'm glad this worked for you. Thanks for watching!
thank you for the video. I'm having trouble in creating a database for my calibration lab and this video is really helpful for me.
Hey I'm glad this has been helpful for you Dyana! Thank you for watching and best of luck creating your database.
Hello thanks for sharing this video.. I am beginner to Ms access.. wat skills i must know in access as a beginner?
Can I import one spreadsheet into Access, then later, add columns to the table that it makes? AND Can I later make a different table and link data from one column in one table to data in another column in the other table. Can an Access DB grow in this manner? Sorry to be a stranger asking questions. Thanks for the video. I'll start watching your other videos. Thanks again, B.
Hi, i used Access back when i was in college, but at work im getting back into it as it should be easier than excel for our data analysis. I'm struggling to get my monthly data to import to the same table. Each month i will have additional new customers and right now i have customer name as my primary key. During my import from excel into an existing table, Access provides an error message stating # records lost due to key violations. Do you have any suggestions on how to best upload data monthly and what to select for a primary key?
Thanks your videos are super great!
U really know how to explain...,I loved the video and I have gotten the concept,,thank you so much
Adam, is it possible to have the linking allow updates in both directions? That is if I can update the data in access it is also updated in excel? Thanks!
Same doubt here
Thank you so much professor this has been really helpful you saved me a lot of time.
Thank you so much. Even though this was not for me, it still helped.
No problem Sabrina, I'm glad you found it helpful. Thank you for watching.
Great videos, if is possible can you do a series on creating a simple Vehicle Maintenance Access database, to keep track of repairs or maintenance, gasoline usage, etc, I haven't found one in the web or in youtube. Thanks
Best tutorial Professor Morgan!
Hi, My name is Tyrone and Love your videos. At age 60 am deciding to build a database in Access. After building a Table 30 fields am having problem importing pdf files or text files in to my present Table in Access and also will like to update and keep the previous data for records to look at. Thank you
Clear and concise!!! Thanks a lot!!!
No problem, Shahin. Thank you for watching!
nice work dude. quick and easy to understand.
Hey thanks scholesy1000, I appreciate that. Have a nice day.
hi and thanks. can you advice how to choose few fields from excell and export it to access. thanks again
Hi.I like your video very much. It's really great. I'll keep an eye on your channel. I am your fan and I will support you.
Thank you, pronounce word. I appreciate you watching, have a nice day!
Dude! Nice job on cutting through the muck and teaching an otherwise sermon to a quick text with an emoji at the end 👌🤣👍 well done sir!
Thanks Professor, I am wondering if you have a tutorial on how to create and run queries.
Hi Hermann,
Thank you for watching. Yes I do, here is the video:
ua-cam.com/video/YWjQcra9dOc/v-deo.html
Very nice video tutorial, I've been search this tutorial. thank you so much. Can you make tutorial for Account Receivable with details of customer? tks.
HI, Great tutorial, I need to import multiple excel files into Access (approx 50). Can you advise if there is a quick way to bulk import? Thanks
Helps alot, thanks.
I'm glad this helped, Elis. Thank you for watching!
@@ProfessorAdamMorgan You're welcome. 😃
Hi. What are indexes? Is it important to know about them in terms of import? Thanks in advance
Thanks man. I realized where I was doing wrong when I watched this.
No problem Ramazan, glad this video helped. Thanks for watching!
Prof Morgan... the access file I am working with has data in the tables presently and is already set up with the tables tied to reports, queries, and dashboard buttons. I want to copy in data from an excel file into an existing table. Trying to save hours entering one record at a time. So when I paste these records in.. will it compromise those reports , etc.?? Thanks for your time sir.
Hi J Cascio,
What I would recommend doing is saving a copy of your database somewhere else on your computer. From there, try to import the information and see what happens. Since it's a copy of the file, it shouldn't cause any problems to the original file. I doubt you'll have issues with the reports, queries, etc. but it's better to be safe than sorry. I hope this helps.
Thank you for this video! I was having trouble importing from excel for my CIS class and this was really helpful.
No problem, Kat. I'm glad this helped you with your CIS class, thank you for watching.
Thanks Adam!!! it was easy to upload excel to Ms Access
Kudos
Thank you Adam. What if my backend has a unique numeric identifier choose automatically by access. How to generate this number
Thank you, Professor Morgan. Great videos.
Hi there, really like your videos. I have a bit of problem. I have data I need to mail merge from Excel to Power Point. Is there a way to do that? I need something similar to Word or Publisher mail merge. Thanks.
All right, man! Thank you for your attention.
No problem Prof. Joaquim, thank you for watching!
Thanks for this video, it's really helpful. Though I think i have a problem with my laptop, when ever I click on the External data, it doesn't show saved import, I can only see saved export. This has made it difficult for me to import into my access. I'm using ms access 16. Pls is there anything I'm not doing? Pls help me out here.
Thanks for the toturial , i am trying to import an excel file but it has merged cells so what should I do to complete the task successfully and import it to access
Hi Dyala,
Thank you for watching. You will need to unmerge the cells and remove any unnecessary cells from the spreadsheet. I recommend only having column headings and data if possible. I hope this helps.
Hi sir , will it be possible to import execl and directed screen flow automatically as per rows and columns
Another excellent set of videos. Could you make a video on merging two worksheets with similar information? I have two worksheets that contain some identical fields, but other information. I would like to get that information onto one sheet. Thank you.
Hi Mike,
Thank you for watching the videos and for the comment, I appreciate it. My recommendation is to export the two tables to Access, then use a Query to get just the data that you want from each sheet. Here is a video of me explaining combining multiple fields into a query:
ua-cam.com/video/YWjQcra9dOc/v-deo.html
when importing data from an excel sheet into an access table... will the imported data mess up things when the access table your dumping the data into is linked to other forms and queries within that access file? (sorry to be long winded) Joe C
Hi Joe C,
No, the imported table won't link to any other data in your database unless you connect it in the future with a relationship. I hope this helps, thanks for watching.
Hello Professor. I am following step by step and for some reason I am not able to append one Table with another. Is there anyway we can communicate via email or directly ? Thx.
Nice video. But I seek to append just one column of data from Excel into an already existing Access table. I somehow had that field deleted from Access and now I want to import just that column. Is that possible?
Hi,
Try adding in a new column (field) in Access and name it the same name as the column in Excel. From there, import the data and it should work. I hope this helps, thanks for watching.
Excellent ... simple and to the point
Hi, do you know how to create a CV from various tables in access?
Adam, thank you for your video! It is very helpful. I haven't used ACCESS for over 20 years and needed some brushing up. So thankful i found your videos.
However, I am coming across an issue. I am importing my excel file and it has several rows that are duplicated in columns. Such as the SSN and Name. The rest is not duplicated. Then, my file won't import. The error i am getting is: type conversion failure.. do you have a video for such error?
Hi professor. Is there a way to import excel files to access all at the same time? Also, can I import specific columns from excel to access?
Hi JohnD Great,
I recommend combining all of the files on to one specific Excel Workbook. From there, you can import it all from one file. You can choose which columns to include in the Import settings.
Thank you very much. Your video explained exactly what I was looking for. Thanks!
Thank you so much professor👍👍👍
No problem, Saepul. Thank you for watching!
Hi Professor,
I have a query from an Access database exported to Excel (so i can manipulate the data) and then a link from that excel file back to access. The problem i am having is that the data going back to access doesnt refresh unless i open and close the excel file. I have changed the setting for refresh in excel under the query and the external data menues but it still wont auto refresh. What can i do to accomplish auto refresh without having to manually open excel?
Thanks so much for this video. It was actually a help for me. I was trying to append an excel file into my access table but I was clicking excel under Export instead of under Import & Link.
I'm glad this helped you out, Upton Kanei. That's an easy mistake to make. I'm glad you got it figured out.
Hello
is it possible to receive datas only from excel?
I mean build the DB & Table at Access and handle daily works at Excel then upload excel data to Acess DB weekly or monthly
I mean I when I try upload from
excel , new table is created at Access while my aim is to upload the data to the table that is already exited in access.
Hi Morgan, I would like to make the price list of parts with 7000 items in access with front page to put the part number only and get all the price and other details in the excel format and then make a list and export to excel, Do let me know we can do
This video saved me lol... thanks for your help! 🙏
Hi Adam, thank you for this video. I have a question - How shall I Import the excel data from a particular sheet into the access if I have more than 1 sheets in the excel file?
Hi Chetana,
Thanks for watching. When you get to the 2nd step of the Import Wizard in Access, you'll see all of the worksheet names listed at the top. Choose the one you want and follow the prompt as needed. I hope this helps!
Heloo. Just want to ask if there's a way to automatic transfer the data from excel to access? Whenever I'll add something on Excel, it will automatically update to my access? Thank you.
This was a really easy and simple video to follow. When I tried it, it worked beautifully. Thanks a lot! BTW, I have an Excel things I'm trying to do, but don't know how to do some of the things I'm trying to accomplish. How can I get in contact with you to see if you might be able to lend me a hand with it, or just give me some pointers?
Hi Dome,
Thanks, I'm glad this was helpful for you. The best way to ask me questions is through the comment section on videos. I try to respond to all comments and help out whoever I can. As for learning Excel, I already have a pretty extensive playlist that you can view on my channel, I'll link it below. Just let me know if you have any questions:
ua-cam.com/video/tkSkIGrijOY/v-deo.html
Supper explanation and very helpful
I'm glad this helped you out, The Best Technical 360. Thank you for watching!
The nail polish is very cute
Thanks Avery, my wife had the other side of this room haha.
Thanks a lot.... This one helped in my exam
No problem Soubhagya, thank you for watching my video!
Hello professor I have a question how can I prevent a customers list from creating duplicate entries? BTW I want to thank you and let you know I have enjoyed watching and learning from your videos
Hi Enrique,
It depends which program you'd like to remove the duplicates from. If you want to remove them in Excel, go to the Data tab, Data Tools group, then select the row you'd like to remove the duplicates from, and click Remove Duplicates. If you want to remove them in Access, make the duplicate customer field the Primary Key. If you do that, Access won't allow duplicates to be made in that field. I hope this is helpful for you. Thank you so much for watching my videos, I am glad you're enjoying them. Have a great weekend!
Professor Adam Morgan I am using access 2010
Thank you for making it so simple. Helped me
Hi professor Morgan!
Your videos are so helpful and helped me learn a lot, thank you!
I had to use Access for the first time at my job, that’s when I discovered your channel and your videos have been extremely informative and easy to follow.
If it’s possible, can you make a video about macros? I’m trying to make a button that would automate Importing and exporting data from Access into Excel and vise versa (basically automating data ingestion for both softwares). I know it can be done but I don’t seem to figure out how to do it. If you can’t find the time to make a video about it, can you please explain the process or give me the formula (idk if that’s what it’s called?) for it. It’s kind of urgent and I would really appreciate it. Thanks again :)
Or maybe if it’s troublesome for you, please just direct me to a video or a website or anything that could help me achieve what I’m looking for.
Thank you, and waiting for your reply :D
Hi Randa,
I'm so glad to hear that my videos have been helpful for you. Access can be pretty intimidating if you've never used it before, so I'm glad you feel more comfortable within the program.
Regarding Macros, I do not currently have any Macro videos for MS Access. I found a website that could help you with this, so I will link it below. Hopefully it works for you, so let me know. I may be able to make a video on this in the future, but right now I'm still catching up on other student content. Have a great day and thanks for watching!
support.office.com/en-us/article/importexportspreadsheet-macro-action-9973a631-6586-4c1d-9c61-ae167696b750
You are the best! Thank you for this video.
Hey thanks for watching MarCel, I am glad you enjoyed this video. Thanks for watching!
Hi bro,Is it works with template MS access Inventory and Billing ?
Hi 888boss88b,
I'm not really sure what you mean by template. Please let me know with a little more detail what you're trying to do and I'll do my best to help you out.
Thank you for this easy to follow video. Unfortunately, I still get the error message when I import - The Microsoft Access database engine cannot find the input table or query. Make sure it exists and that name is spelled correctly. How do I fix this? SOS 😅
Is there anyway around importing to a current database from a Excel file that has all characters formatted correctly...but the columns are in a different order? Great vid!
Hi TheParamedics1,
I'm sorry but I am a little confused by your question.
Typically you can order the fields however you'd like once they're in Access. Please give me a little more information and I will try to help. Thanks for watching the video.
Professor Adam Morgan Wow, thanks for the quick reply! Apologies if my terminology is incorrect, my situation is I have a Access database linked to a website that houses ticket records for that are distributed and worked at my job. These records are currently being mass imported from an Excell table via copy paste from the table to Access by selecting the New empty row below current data in access table. The excel file the table is copied from is formatted accordingly by text and column order or else it will not take the data and cause an error. The raw data that is filtered to create the final report in excel has extraneous data and columns. My current workaround to clean up prior to Access import is a formula to dump the data in a template excell that will automate the correct formating and order for access importing to another sheet in excell. Is there a faster workaround to obtain the correct formating, specificly only taking certain columns needing for import to access? The text is in correct format without an issue, but I need a faster way to format or get access to take the incorrect column order. Apologies for the long post, hard to explain jajaja 😂
No problem, I'll do my best to help here but it's kind of difficult to do over text without seeing the actual document. Linking the data to Excel is not always the best option since if the Excel file is lost, moved, or changed, it can effect the data that it is linked to. I guess the question I have is why not just add the ticket information directly to Access in the first place. If you get it into Access from the start, it will fix the linking issue. I'm not sure how much data is contained in the columns, but if it is not too much to be manually typed, that is always an option. It seems like your current copy and paste method takes time, but works correctly. I'm just wondering what is the point of using both programs as opposed to one or the other. Let me know.
Hello prof! is it possible to import data from excel file which have the same PK with the Access table? so we are like updating the already existing data in Access instead of adding a new records
Hi Abdullah,
You're probably getting an error because of the following:
1. The primary key you specified has duplicates, and therefore cannot be a primary key.
2. The primary key you're using is in use in another table in the database.
It's a little hard for me to answer this question without seeing your database, but I hope this helps anyway. Have a great day.
Please help me with a video on computation of parameters
so what if you already have data but might have new data in the excel form and you just want to update the access database without duplicating information that was already updated prior, can that be done?
Hi, I hope you can read this. I am trying to import an XLS file but the characters are in Korean. And when I import it to the database, the data is in "????". How do I get MS Access to be able to read Korean characters? I have already tried installing Korean language under MS ACCESS options. Thank you very much
Hi Cedric,
I'd recommend translating the information into English before you export the file. I'm not entirely sure that the import will work in other languages. I know Word & Excel are supported in many languages, but I'm not sure how an import would effect that. I hope this helps.
Thank you so much for the tutorials. It has been so helpful.
I'm glad the videos have been helpful. Thank you for watching and have a nice day!
I am doing an append and getting a script out of range is that due to the headers?
Hey, I have a question, how do you import a spreadsheet with more than one worksheet? Can you please make a video based on it? Or if not please tell me how to do it. Thanks
Naeem
This video helped a lot. Thanks
Hello Prof. Adam, how do I cross reference my data from excel to my existing database in access? I want to know if my data from excel have the same matches in access. Thank you and great video by the way.
Hi Bruce,
I suppose you could convert the Access to Excel, then go to the Data tab and click Remove Duplicates. This should show you all of the examples of duplication. If you see 2 of everything, then they have the same matches from Access. Below is a video on converting Access to Excel. You may find a better solution than this with a web search, but I suppose this way would work.
ua-cam.com/video/qXU-nzVPvMs/v-deo.html
By the way, thanks for watching!
Thx a lot. I'm from Sri Lanka
Hi Adam, Great tutorial!! I have a table in Access that I took and added more columns to it in Excel. Now I want to link it back with the new columns to Access, what is the best way to do so? Also, I don't have a primary key in excel or access data, so I am afraid that when I link both together, they won't be correctly sorted. Is there a way to fix that? Thank you so much
Hi Ali,
Thank you for watching! I would import the Excel table back into Access as a new table, then delete the original. The other option is to add the columns to the original Access table and append the columns, which can be tricky sometimes. If your tables aren't linked with a relationship, I wouldn't worry about deleting the original. You can always resort the table in Access, or create a column in number order in Excel and use that as the primary key. You have some options here, try a few different ways and see which is the best. I hope this helps.
Thank you for you easy to follow Access 2016 videos. Im new to Access 2016 & I need help importing an Excel 2016 spreadsheet whose columns names have changed but the data is mostly same the already existing already existing Access 2016 database except for 1 new field in the excel spreadsheet. There is an access form with a macro button that imports the excel data into once a 2nd macro is click that confirm the data shown after the import button is clicked is correct.
Is there a way to mport the excel file into the Access Db then create a macro or update query to change the excel field hearings to match the current AccessDB then just add the one new column? Thanks!
Hi Juanita,
Thank you for watching my videos, I appreciate it. You could set up a macro that makes the import steps, but unfortunately, I don't think you can link it to the Access file. I'm not too sure how to solve this problem, and I don't want to direct you to an answer that doesn't work. You could run an update query, and link it to your Excel data I suppose. As long as the file locations do not change, but I haven't tried this myself before.
@@ProfessorAdamMorgan Thank you for the update Professor Adam...Thankfully I was able to do a work around by manually formatting the filenames b4 Iimported them then only copying the data fields not the headers when I import the data.
I do have another Access 2016 question...I want to turn on the checkboxes in the Database Tools/Relationships section so that all my tables that have a primary to foreign key /1 to many relationships have the 2 relationship properties checked: Cascade updates & cascade deletes. But both if these options are greyed out on all of my tables with relationship lines. Do you happen to know what I can do to enable both?
thanks
Juanita
Hi Juanita,
I am glad you were able to figure it out. You need to create a one to many relationship to enable both of the cascade options. I made a video on this a while back, have a look at it at the link below. Hopefully it'll help you solve this problem:
ua-cam.com/video/NtRAyS0LLlk/v-deo.html
Thank you sir for this. but i have an issue where it stated that field ‘f5’ doesn’t exist in destination. however in my excel and access just consist of four fields . can you help me? tqq
So helpful! Thank you very much!
I'm glad to hear it linhlan, thank you for watching!
Very useful. Thanks.
I'm glad to hear that J Rabinow, thanks for watching!
Problem: Example: KeyID is contained in several lists, but some columns are same, some are different. LIST "A" : Customer 1001 Doe | John | 123 Maple Street | Anytown | AZ -- - - - LIST "B" Customer 1001 Doe | John | 333 Pine Street | Anytown | AZ | Purchase date 1/28/19 | Attribute 32 | Attribute 07 ? Would I have to add columns to match so that ALL imported Excel lists are the same? Some Lists have 100 columns, some have 12. If the customer had two different addresses, how would that be handled? You may have video already, I have not found it yet. Thanks!!!!!!
Hi Sam O,
If you're trying to append a table, meaning combine Excel data with a current Access table, all columns and field names will have to be exactly the same or it will not work. If the Excel data has 100 columns, you'll need to make sure that all 100 columns are included in the Access table in order to append. If you're trying to send the Excel data to a new table, you can do it by creating a new table within your Access database. If the customers have 2 different addresses, you'll need to create a column for both addresses, rather than having them in separate rows. I hope this helps you out.
It was really helpful
I'm glad to hear that Fatiha, thanks for watching!
nice explanation... but how many cars do you have???
No problem Sreejon, thanks for watching!
Thanks for the info. I have a question: what happens when the existing database has an autonumber column? Do I have to make sure the excel sheet has no over-lapping numbers? Basically, I'm trying to copy a bunch of records, change an aspect of them and reimport them to Access, but I'm worried about the autonumber column. The reimported data should not have the same id numbers. Do I have to adjust them in Excel? Anyway, thanks!
Hi Warwick,
The autonumber that is placed on the new Access table is the table's primary key. This means that no number in this column will have a duplicate value. I made a video on primary key's and what they mean and what they do. You shouldn't worry about it too much, it's just to reduce errors in databases. You won't have to change any of your Excel data before the import. Here is a video about primary keys:
ua-cam.com/video/7lJS5tklOrE/v-deo.html
Nice thanks...easy to follow
THANK YOU SO MUCH FOR THIS INORMATION
Tip if you have one of the newer versions of excel make sure your clicking in the part where it says imports.
Hi Ibrahim,
Thank you for adding this tip, I appreciate it.
Hello Professor Adam; You are my favorite UA-cam teacher that I have found. I would like to know why my Ribbon bar is not active for me to import a workbook from Excel to a table. I click on the External Data icon but the other icons are in color gray. I will apréciate your help.
Brother how can we transfer data from ms access form to ms excel in a new sheet. Means when we fill the form in ms access and click on submit button and the data should be save in ms excel new sheet. Please guide me
Hi Ashish,
Sure, here is a video I made on transferring Access tables to Excel, I hope it helps you out:
ua-cam.com/video/qXU-nzVPvMs/v-deo.html
subscribed and still surfing on ur channel's videos (Access), have a great day!
Thank you for subscribing Deny, that really helps out my channel! I'm glad you're enjoying my Access videos!
very helpful, thanks
Glad it was helpful!
Hello, I have a question regarding updating files /adding new data into an existing excel file when there is a connection with pivot tables. Whenever I try to refresh a sheet (when I add new data into the datasheet), my file doesn't want to refresh and says: error, cannot open the source file called "xyz". Is there any possibility to update a file and that the pivot table updates automatically without this error. I don't want manually do every month new dashboards. Thank you in advance!
Hi Ivana,
I'm actually not too sure on this one. I would recommend converting the pivot table to a range and seeing if that helps. I'm sorry, but I typically try to make the Excel formatting as basic text as possible before I convert to Access. I hope this helps you, thank you for watching!
Hi Adam, great tutorial which has helped, however I have a linked field in the table I wish to append (linked to another table in the database). When I do as you've described the linked field is empty. Is there any way of importing that information ( I have 12,500 records to enter)
Hi Susan,
I'm glad this tutorial was helpful for you. If you're trying to append a table from Excel, verify that the data type in Excel is the same in the original Access table. For example, if you're using currency in Excel as your number format, make sure currency is the field type in Access. I hope this fixes your problem.
Thank you, will give that a try.
Best of luck!