Hi,
I am creating a database in Access which will use data imported from flat Excel database - this excel database is very wide with 255+ columns. The source Excel database will be often updated as raw data is run through Power Query for cleaning and the source database will be the PowerQuery output. In importing the Excel data into Access, I ideally want to only import certain columns into each of my individual normalized tables in Access. I would like to do this through linked tables so that the database in Access can be easily updated as the Excel spreadsheet (where the data is pulled from is updated).
The questions I therefore have is: 1) What is the best/simplest way to import just certain columns? Is this possible? Or do I have to split my Excel database into normalised tables first?
2) Once I have imported data through linking - I understand that the tables cannot be amended in Access. Can I still merge or append tables together through primary/foreign keys? Or will I have to import files to be able to do this?
Thanks.