I need some advice on which program to use for a file I have to build. I know I could do this in Excel with few problems but the person who assigned this to me (who isn’t my boss but does rank me), thinks I should use Access. If I use Access it’s going to be a pain because I just don’t use Access much and have never used it for something like this. Unfortunately, I don’t have to have it done until the end of the first quarter so I can’t use that as an excuse.
Here’s what needs to be done.
Build a form for the entry of 7 fields of data. There will be some drop-downs for user to choose from for most things. If a certain selection is made in one drop-down, that will control the selections offered in subsequent drop-downs.
One field will be a date.
One field will be a numeric entry. This field will have to have a warning message if the user enters an amount that may be too small, but they will be allowed to continue.
If, for example, the combination of fields 1, 2, 3 & 4 are duplicated by an existing record, the entry will not be accepted.
The user may need to bring up a previous entry to adjust or correct it.
The output will be a key combining all of the entries into a string which will be uploaded to the mainframe as a flat file.
We’ll also want to do queries for particular states/companies and create reports. This is the only part I really know how to do already.
Which is the better tool for this? Access or Excel?
One more thing. It’s also going to have to be sorted by Field1&Field2&Field3, then an additional field will have to calculate the cumulative change in the numeric field, until the value of Field1&Field2&Field3 changes, then it starts over.
Access is much better suited to your task hands down. You could probably make it work in Excel but when you have “access” to the correct tool for job, why force it?
If you know enough VB to do this in Excel then (skill wise) you should be able to do it in Access. Just reading your specs this could be done in Excel but (IMHO) Access would be the better tool, mainly because of the reports.
Really it all comes down to what you are most comfortable doing AND what everyone else is most comfortable with. If everyone else feels Access is best and they are best suited to maintain this in Access that Access it shoud be. On the other hand if everyone is a power user of Excel and can work with forms and VB in Excel then maybe that is a good choice. Unless you work in a very small shop (2 or 3 people) it is important think about application development in terms of future maintainability, not just I have to get this done today.
I agree with Opengrave. Actually if you have an VBA skills in Excel then you should not have an issue in Access.
Again the need to create reports or output in a ‘flat file’ is probably best suited for Access. You have valiation rules for the inputs and forms. For your requirement
You could set the 4 fields as primary keys. This will prevent the user from entering duplicates of those four fields.
My .02 cents, definitely access is the way to go. What’s going to happen is this, once they powers that be realize that the data can provide them with important information six ways to Sunday, they’ll want bigger and better and more complex reports, which access can handle quite readily if your tables are set up properly.
I’m not too concerned about it growing too much. They aren’t going to be able to do much analysis with this information on its own other than glancing at rate changes by state & line & getting a cumulative number out of it. The premium file my flat file is used with is way too enormous to do anything with on a PC.
But, since the concensus is that Access is the way to go I’ll give it a shot. Thanks for all the advice.