Would an Access database be useful to convert to from Excel files?

[ziggyztz]

My company currently runs on Excel. Practically everything is run in some Excel file or another. We have several very large reports that need to be updated at specific times throughout the year. These reports basically have 1 summary page that pulls data into it via VLOOKUP's and location settings picked from a dropdown List. On top of that, rather than have client specific files reference this large report, each file has to be updated separately with the exact same data. It's a colossal time sink, and while I think Excel does wonderful things, having EVERYTHING on it is wasting time and opening potential for errors. Each client currently has it's own file because we send these to clients and whoever made them was trying to keep the file sizes smaller.

I don't know a lot about Access so I just want to see if it's feasible to create a database where we have what the summary should look like and simply upload whatever source files there may be for each year and add a new option to select each time new data is added. Eg. SummaryPage would have an option to select year that could pull information in a similar fashion to a VLOOKUP from Data2014 or Data2015, and when 2016 rolls around, I could upload Data2016 and add that as an option. Then I could setup one summary page for each client's information that pulls from the same data sources based on year selection.

There would be a bit more to it, but I think if the above is possible then it should be able to handle any other criteria needed such as multiple criteria selection (if year is 2015, then have options to pick quarters from 2015), multiple data sources, etc. Is this possible to create with Access? Are there any things I would need to look out for or simply wouldn't be possible? Or is there an easier solution in continuing to use Excel?

[alansidman]

Suggest you read this:

http://forums.aspfree.com/microsoft-...el-349267.html

[CJ_London]

the link posted by alan is spot on.

However your thinking that 'simply upload whatever source files there may be for each year' for a db may be flawed. Almost certainly your files will be 'short and wide' - the excel way, whilst db's have multiple tables which are tall and narrow. Short and wide can be notoriously difficult to manage properly in a db. Part of your feasibility study should be to investigate whether you can get source data from further up the chain - almost certainly it will be a db of some sort so you can get the appropriate tables rather than the excel style format you are currently receiving.

There is a steep learning curve for db's of any type and Access is no exception - but generally worthwhile for the right task. I've recently completed a project to replace an excel process creating some 750 personalised reports every week to one in Access - processing time considerable reduced and fully automated, error handling much easier to manage, much easier to maintain, much less resource required.

[Richard Buttrey]

Hi,

Those are extremely pertinent points that Alan and CJ (not the MD of Sunshine Desserts by any chance ) have mentioned.

I'd also mention the other major difference and that's that Excel is essentially a single user system or to be strictly correct a system in which only one person can update the workbook at any one time, many people can still open the same workbook and use it as read only. I'm ignoring the 'workbook sharing' functionality which nominally does allow more than one person to update the workbook. A lot of us think this functionality brings too many problems to be of any serious use and in any case there still needs to be an arbiter to determine which of two changes made whilst the workbook is open should take priority when the workbook is saved. Access of course is a true multi user system with record locking functionality to prevent the same table/record being updated at the same time.

That said, and from your brief description I would suggest that unless you do need many users to update records at the same time it's perfectly feasible to create a 'proper' database in Excel and do all the stuff you mention. It's perfectly possible to upload user files from elsewhere and if the file layout is not in the sort of layout that the Excel 2 dimensional database requires a macro that runs immediately following the upload to a temporary sheet should be able to reformat it into the correct layout for the database and then add to the database.

It sounds like your existing system was not put together in the most optimum way which is why you struggle at the moment. An Excel database which contains all client details for all periods would enable you to extract an individual client's details at the push of a button.

[CJ_London]

not the MD of Sunshine Desserts

Nope but it has been mentioned before...

[TMS]

I think all the points made are very relevant. I especially like the link that Alan provided; very concise ... and a useful warning

Aside from the points mentioned about learning curve and design considerations, you might want to think about licensing issues. Most organisations will generally have standard Office applications on the desktop, like Word, Excel, PowerPoint and Outlook ... and a few other oddities like OneNote and Publisher. What it probably won't include is Access. It used to be relatively expensive. I am seriously not up to date with understanding the licenses required to read and/or update an Access database but may be worth looking into. You'd need to consider how many people will need to use the database, etc.


Regards, TMS

[CJ_London]

Access comes as standard with most office products except home and student - so a professional organisation is most likely to have it already. However some IT departments won't support Access (but then they don't support Excel either in terms of what's in the spreadsheets) because they don't want to be responsible for something that has not been developed by them. I think standalone access is around $60 which the developer will need but users can use Access runtime which is a free download. Runtime has the ability to run applications already developed in Access but is missing certain functionality - including the ability to develop or change the application or visibility of certain windows such as the navigation pane.

The thing to remember about Access is that it is actually two systems bundled into one - a backend and a front end. The backend consists purely of tables and uses the ACE db engine (JET for 2003 and earlier, no longer supported) and for multi users is typically located on a shared server whilst the front end consists of forms, reports, queries and code and each user has a copy (in the same way each user has their own copy of Excel or Word). So you can use access as a front end to another db engine such as SQL server or MySQL or facilities such as SharePoint or Excel - in fact pretty much any data source. One of the nice things about this is you can write queries that combine data directly from say MySQL, a native access table and lets throw in a couple of excel workbooks and a text file while we are at it.

[Richard Buttrey]

Thanks CJ

I hadn't realised this 'split personality' aspect of Access.

[TMS]

Thanks for that clarification ... so, just back to the learning curve and design considerations