Excel to Access

[mrafiq44]

Can journal detail from 10 divisions in excel exceeding 100,000 lines each be exported into access, so that all data can be viewed in one database at one time instead of opening 10 different worksheets?

 

[Joe4]

Sure. Access has an overall database size limitation, not any record number limitation.

Assuming the layout, structure of all these Excel files are the same, there are a few ways you could go about this:
- import all 10 files into a single table in Access
- link all 10 files to separate Access tables, then use an Append Query to sew all of these together in a single query (see: this would only add up the 1st item and then the item from the vlookup. i was needing to add up from the 1st item THRU the item in the vlookup.)

The advantage to the second option is it doesn't require you to import everything. So, if your data files are constantly changing, and you need to do this multiple times, the second option may work better. All you need to do there is replace the Excel file each time (with file of the exact same name in the exact same place).