Microsoft Excel is a fairly powerful program that gives us the ability to create records, tables, statistics, lists, and more; even to automatic forms with the use of macros. You can download Excel from its website or use the online version, buy the digital disk or the mobile version.
Although the program is already quite good on its own, it is possible to get more potential out of it if you use VBA, if you install plugins or if you use it in conjunction with other programs such as Microsoft Access, which belongs to the Microsoft company’s Office suite, We will teach to cross data.
Index( )
- How to move my data from Excel to Access?
- Clean data before import
- Connect Access data with Excel
- Split into organized topics from Excel
- What is the procedure to choose the best data type when importing my information?
- How to automatically append data from Excel?
- How to compare two tables in Access to find only matching data?
How to move my data from Excel to Access?
One of the limitations of Excel is the impossibility of efficiently crossing information from two data tables, that is, that the data from a list with several repeated names is registered or combined with that of another list to give a totalization of all common and non-common data. This happens because Excel by default, what it will do is always take the first information of the repeated data, ignoring the others.
To replace this problem is that it is necessary to take the data from Excel to Access , and then combine the repeated data into one and take them to the main table, once this is finished, the data is exported from Access to Excel and the modifications are made. relevant once there.
Clean data before import
First of all, it is important that the tables that will come into play in this task must be as organized as possible and their data ordered, any other necessary and irrelevant information must be eliminated to avoid errors that may arise in the data crossing .
Likewise, you verify that the usual information, assuming that it is a list of employees, with payments, debts and other financial matters, IDs, names and others, are exactly the same in both tables, since this can cause problems when crossing the information.
Connect Access data with Excel
Once the above is done, then touch the connection between Excel and Access, to export the spreadsheets from one program to another. The spreadsheets in Excel must be named, this so that you have better control of them.
Proceed to open the application and create a new database and rename it according to what you need, now, from the “External Data” tab, you will see the “Excel” option, this is where you will import the file; when you do, you will have to choose a series of options that Access will ask you; in the first instance, we recommend that you leave everything on default. You must do this as many times as you want to cross spreadsheets.
Split into organized topics from Excel
Excel has many formulas and functions, such as the Excel function BDVAR and BDVARP , which are widely used in statistical databases to calculate demographic differences . With this software you can also organize your information according to themes, which is highly recommended if you are going to cross data between tables.
What is the procedure to choose the best data type when importing my information?
As we already mentioned, if this is your first tour of Access , use the default options to avoid possible errors. Similarly, when you import an Excel file to Access, you will see a dialog box, choose the “Show worksheets” option, so that it will show you the spreadsheet tables correctly.
Also specify, if so, that your tables have headers and that they are in the first row, so that Access does not misplace them with the true information, then it modifies the information of those tables that will be imported, because fortunately the software It allows you to eliminate those columns that you think you do not need for data crossing and finally, the option “Without primary key”; this procedure with each spreadsheet as well.
How to automatically append data from Excel?
A particularity of Excel is the possibility of automatically filling cells if they follow a logical series, such as a sequence of numbers from 1 to 1, 2 to 2 and so on; even the alphabet, or repeated data; To do this, what you must do is place the sequence in the first two boxes and later, click on the lower left corner of the second box and drag down or to the side depending on how long you want to fill and that’s it.
How to compare two tables in Access to find only matching data?
Using the Excel Database BASE function is for calculating the bases of a number, and we specify the details, very useful for logarithmic calculations in Excel. When you have entered all the tables within Access, just below the task bar, two dialog boxes will appear in which the headers of your tables will be and from here it is that you will be able to compare, differentiate and finally join the data, selecting those specific what do you want.