Question during a “drop in” session: I have an Excel Workbook with several large worksheets. Three worksheets contain over 100,000 rows, one contains over 80,000 and another 23,000 rows. I need to match up the IDs and place the information from two massive worksheets into the one with 23,000 rows and I can’t figure out how to do it without having to search for each record manually.
Answer: I was possible to use a VLookup formula, however by the time I got to the third column to match and run the formula with such massive data, Excel “choked” and I was not able to save the results or even complete the task. So I went to my favorite application – Access. I imported the three tables into Access, then I created a query joining the common ID field, and selected the desired files, and got results in a matter of seconds, Then I exported the results to Excel. This was SO much faster and easier in Access than using Excel, in my opinion.
This was a question from a medical student during one of our drop in help sessions. I love challenging questions (and simple ones too) and helping people with their projects. These drop in sessions are a good opportunity to get one-on-one help on a variety of projects. See our schedule here.
I have data in an Excel spreadsheet that I would like to use in an Access Database. What are the steps?
Importing or linking data from Excel to Access works well if the Excel data is in rows and columns without any subtotal rows, formulas or blank rows or blank columns within the worksheet or range. Column headings should not be wrapped or contain special characters and must be unique names. Then the data should import pretty cleanly by:
- Create a blank database in Access (or an existing database you wish to place it) and select the External Data tab > Excel (from the Import group).
- Locate the Excel file and select “Import the source data into a new table in the current database” or “link to the data source by creating a linked table.”
- Follow the screens until the process is finished.
If you have cells which contain formulas, they will import as values. When importing, the Access table is completely separate from your Excel file. Any updates made in Excel or Access after the import are not updated in both applications. However, if you “Link” the data, you can only update from the Excel file, but the data is automatically updated in Access so that it can be used for filters, queries and reports.
Keep in mind that just because your Excel data imports well into Access does NOT mean that your file is set up ideally to work in Access. You may have to “normalize” your data – add fields, split the data into more tables, add primary keys, add validation properties, etc. In addition, Microsoft Access requires a considerable investment in time to learn how to use it, compared to Excel. But Access works well if you have a lot of data in separate but related tables to analyze. To learn more about database design concepts, come to ITS’ mini-session on Thursday, 11/29 from 2-3 p.m. in the ITS training room, 8011 Wittson Hall. Additional Access training is available in December. Click here for the ITS class schedule and on-line registration.
Question: I have a number of Excel spreadsheets and Access tables with similar data that I would like to merge into one large database to make queries and reports. What is the best way to do this?
Answer: This is a more complex question to give a quick and simple answer without knowing what the data looks like. Basically, if the data has the same headings (fields) and field types, it should import well into an Access table. In looking and this user’s data, even though the headings were similar, they were not exact the same. They needed to modify the headings to be sure they were the same in all tables involved. Then the Excel files can import into the master Access table. If the old tables are in Access, you create an Append action query and select the old table with the fields you wish to append to the new master table. You may want to unselect primary keys in case ID fields are used in individual tables. You can then use a Query Wizard to search for duplicates or unmatched data in a table. In many cases, however, more one-on-one help is required to assist users when moving data from one application to another, especially if it involves several data sources.
Question: I’m trying to count only unique records in an Access table and can’t seem to figure it out.
Answer: You need to create a query containing the fields that are duplicated. (If there is just one record with an item in a field that is different that will consider that record unique). Then in the query options (right click in the light gray area of the query) select Properties and change Unique Values to Yes. Then run the query and it should show the unique records, from which you can click the Total button on the Home tab of the ribbon and select Count from the bottom row of the query results screen. We will show this query example in our Queries part 1 class on Thursday, July 21 from 10 a.m. – 12 noon.
Question: I my database I would like to fill multiple cells with the same info. In Excel there is that lovely function where you highlight all the cells you want to fill the same info and do a fill right or fill down select and it’s like magic. I am not finding the easy way to do this in Access but there has to be one.
Answer: That feature is not really available in Access, although you can create autonumber fields that will automatically add incremental values. But if you want to fill in a table with repeated values, you must add the field individually or create an update query. You can however, save typing by pressing Ctrl ” to repeat the contents of a field in the previous record.