Using SQL to merge .xlsx file and over 1,000,000 rows?

I'm having trouble with an operation that I believe is limited by the shear size of the data - the operation is a merge and filtration of two Excel workbooks, each with over 1 million rows.

Perhaps there's someone willing exercise some mastery here to help me understand the route I should take here. I'd like to use SQL in Microsoft Access.

The two workbooks are vehicle data and accident data. They both contain information related to road accidents in the UK. The vehicle workbook offers information related to each vehicle (accident ID number, type of vehicle, speed of vehicle...). The accident workbook offers information related to the accident (accident ID number, long, lat, casualties...).

I want to combine the two workbooks into one workbook using the vehicle data as the primary workbook. So, add information to the vehicle workbook rows from the accident workbook rows using the unique accident ID numbers. Note: they both have unique accident ID numbers however, the vehicle workbook often lists the unique accident ID numbers multiple times because there are often multiple vehicles in road accidents (28 vehicles is the largest recorded in this dataset). Therefore, the rows would need to call and subsequently list the accident workbook information more than once.

I think that it would be best to lea a bit of SQL because I believe that SQL can perform my second task synonymously.

Task 2. I'd like to isolate a particular vehicle from the dataset after it's merged. I'd like to isolate the bicycle data, that includes all information in one row to build pivot tables and plug into QGIS.

So the end product will be a single table of bicycle crash data that has all data for each incident derived from two workbooks.

Could anyone help get me started?

From what I understand, I need to combine the two as linked servers, the use TSQL to perform the filtration.

Recent Questions...