excel or access for this task? (1 Viewer)

[stefanocps]

Hello i had triedto solve this problem with excel till now, but may be access is it more quick
I have, monthly, a report from the bank, an excel file with all movements (incoming and outcoming)
I need to filter all the movements and get only the outgoing
For each movement i need to refer to other data that will describe what type of movement is
Normally there is a first choice, and this choice open more choices
At the end each movement will have a general area description and a specific description
I was doing with excel, because what worry me is the import process from the excel bank report file
Any "stupid operator" can copy paste in an excel file..but access seems but more compliceted?
I attach the bank report file (called defelice) where in 1st sheet there are all movements and in 2 the filtered one . In 3rd sheet there are all the description needed
I attach another file with my firt attempt. You 'll see in first sheet a list of movement and in E2 and F2 a dropddown that describe the type
this is not totally good, as i want to choose from the dropdown reading a description, but i want to store the code (a number) associated
What do you think about excel/access?
thanks

 

[Pat Hartman]

I would always choose Access over Excel for managing data.

You can link to spreadsheets as long as the spreadsheets are organized like tables. Then you can use an append query to copy the columns you want from the spreadsheet to a table.

When I import bank data like this, I include some safeguards in the process.
1. I keep a log of the file name which will always include a date. This table assigns a "batch" ID as I log the table.
2. The app ensures that the date of the file is > than the last file imported from that institution and the difference is just a month.
3. The append query includes the assigned BatchID as it appends the data. Therefore, if there is a mistake and somehow data gets imported twice or out of order, I can back out the original import and do it again.