TR Forums

I have a project for work where I need to sum all invoices (say for a specific week) and break down the totals of certain categories. (e.g. Total Repair and Maintenance codes over that time period)

Problems:

a) .dbf files? Access can peer into a couple of them but not the majority.
b) They are not willing to spend money on software so things need to be freeware
c) This tracking will be implemented over a number of stores and if you ever want to see a shining example of non-computer-literate people...

Any ideas? I imagine a little GUI with radio buttons and a database query behind it, but WTHDIK (but what the hell do I know)

Well, if they're genuine .DBF files (aka DBase or FoxPro) then Access should be able to open them. Even Excel should manage that. There's a developer's kit for Access that lets you create standalone, redistributable apps so you could use that; alternatively the same thing could be done with FoxPro or VB (I think you'll need at least the professional version of VB to get the database bits you'll need). Access is easier and makes for better out-of-the-box database UIs; VB gives you more control. FoxPro is kind of in-between, and probably isn't the best choice unless it's the only thing that can open these files. What error do you get when Access can't open them?

As far as UI goes, you probably want to go with listboxes rather than radio buttons. That way you don't have to know in advance how many items there will be to choose from -- just fill the list and let them pick. That works for choosing categories and even weeks (though there are some nice calendar controls that make that a little whizzier and less-error prone).

Well if the files can be opened via Excel, which I just found the main one can be, then I will just make a Excel sheet that can sum and list appropriately. I imagine Excel being able to do a fair amount of 'programming'

That solves a) opening the files b) the money for software (the company uses Excel) and c) idiot proofing it. The users are somewhat familiar with Excel and can just plug in the search parameters.

Thanks. Any advice on how to program this in Excel or will I find the answers easily enough in an Excel book.

Actually, if you take full advantage of VBA (Visual Basic for Applications) bundled with Excel, you can write quite elaborate programs complete with forms/dialogs, custom controls, error handling, etc. For your purposes that's probably not necessary, since you can set up filters/queries right in the sheet. You may want to set up documents with AutoOpen macros that do some of the setup to simplfy things; you probably also want to make sure you're operating on read-only versions of the data so that your users can't go messing with anything accidently. Check out everything on the "Data" menu in Excel.

Those are all good ideas. Thank you.