Combining access and excel

Today my team keeps multiple excel files full of transactions data with categorical data describing the transaction and then numerical values of the transactions. Transactions are sourced from multiple departments so the live in separate excel files and get copy and pasted together. There's maybe 40k transactions and then another 5k per month.

Is there a way to make a single access database that we could load these transactions into?

In my previous Access experience even loading 500 rows into Access has led to crashes and a terrible experience. Access for me has always seemed to transform my otherwise excellent computer into something from 1995 and I have no idea why. I know there has to be a way

In an ideal world we build a master database of transactions and then can use an excel file which has report templates and calculation macros to call forth the records needed for the report. Like a user could select March or April and it would pull out all relevant transactions from access. Is this possible? The key is that it would be auditable as well.

If so is there anything I can watch or read to learn how?

Put simply, yes this can be done. In fact, building tools like this is what I do for my job.

If your computers are networked with a shared drive you could have transaction data sent directly to the database rather than have to collate it from multiple files.

Perhaps a good place to start would be to attach some dummy data and your report/output, then we can help you put something together.

BSB