Access / Excel crossover: Should i attach spreadsheets to records

Question

I currently have an archaic system of client records that I am trying to improve.

For each client i have a directory, in that directory i include a directory for each job. Each job has a spreadsheet that i use to store their personal details, and run calculations and costings specific to their needs. In turn I also have word documents that are linked to their spreadsheet which automatically update accordingly. The spreadsheet is also exported as a pdf as well

I am trying to build a database of customer records in Access, straight forward enough. For each new customer i need to be able to add the appropriate spreadsheet to their records, update the spreadsheet accordingly with their details, use the spreadsheet to calculate their costings etc.. I do not want to enter the same information repeatedly, and would like a cohesive system, with data being passed between access and excel.

Should this be easy enough to do with the two packages?

Answer 1

Hi Noel,

Based on what you have described above I'd like to offer the following comments:

1. The move to an Access database is definitely a good idea and would be more efficient to how you are currently creating/storing client records/jobs.

2. I would suggest designing the database to serve as a job management & billing system too - that way you could do away with need for the individual spreadsheets. If you used the database to record/calculate costings for clients you could then also design the necessary queries/reports to do away with the Word documents as well. To give you an idea, perhaps check out this Office template: http://bit.ly/bXLXhl

However, to specifically answer your question: Access 2007 onwards has an 'attachment' field type for records that could be used to do what you're asking. But, re-iterating what I mentioned above, I don't think doing it this way would provide you with any of the enhanced benefits of using a database.

Regards,

David