Planning/strategy?

[Bryan021]

Whilst I'm new to Access, I'm reasonably proficient with Excel and VBA (thanks to some books and the Ozgrid forum).

In a couple of years I took a company from pen & paper, and some very rudimentary use of Word and Excel, to a quite automated job log and proposal generation system, and basic inventory and price listings.


I use forms, and lookups between workbooks, all working nicely for the proposals, but the shared workbook for the schedule is a can of worms I want to avoid.

Our company is growing (nice considering the economy) and I want to streamline things as much as I can to avoid hiring any more staff than absolutely necessary, and make life easier for those that we do have.
I also want to capture real data of whats coming and going through our doors to help understand the business and plan better.

So my feeling is Excel (as great as it is) will soon become inefficient at dealing with the overall project.
Time to look at Access and start a new learning curve.


The big plan is to have the following in a DB system.

• Customer Info
• Job Log
• Proposals
• Scheduling
• Inventory
• Links to accounting(?)

We have six workstations on a LAN (~2008 vintage Dells), one has an external HDD shared as "Z:"... all "My Documents" are on this drive, and it's backed up off-site daily.

From what I've read to date, the key to a good DB system is normalized tables, relationships, and planning for the future.

This is a sizable project that is going to have to be developed and implemented in stages.
So my initial questions are;

Is it more efficient (less taxing on the system) to have smaller tables with limited fields, or larger tables with many?

Is it better to develop this in a single large DB, or do it in several smaller DB that are linked?
IE; Should I do inventory control, scheduling, and proposals in separate DB, even though they will eventually all be interrelated and share common data?

Thanks an advance for your input... I'm hoping to glean much from this forum (as I have from Ozgrid for Excel).

[ajetrumpet]

Is it more efficient (less taxing on the system) to have smaller tables with limited fields, or larger tables with many?

the purpose of a database system is to use many small tables and use relationships to eliminate the need for Excel.

Is it better to develop this in a single large DB, or do it in several smaller DB that are linked?

I have never seen a legitimate reason to use multiple db's in access and link them, other than a FE/BE scenario. But I don't have experience in every arena, with regard to Access, so I can't say that there would never be a scenario where you would NOT want to do this. But I seriously doubt your situation calls for it.

IE; Should I do inventory control, scheduling, and proposals in separate DB, even though they will eventually all be interrelated and share common data?

[/quote]absolutely not. All of this can be in one. More than one uneccessarily complicates things.

[Bryan021]

Thanks, Adam.
That's what I was hoping for.

Due to the size of this project it's going to take some time to complete (more so for an Access newbie), but I would like to start using portions of it as soon as possible to show something for the time spent.
The thing is, following logical progression of the development, the things that will be ready for use first will just duplicate what we are successfully doing in Excel, and will not show an initial benefit.

The customer info and job log will have to come first as this is what everything else will draw on... hopefully I'll be able to import the existing data from the current Excel spreadsheet to complete this phase quicker.

Once to this point, I'd like to concentrate on the scheduling aspect, as this is what we're in most need of improvement.
The existing spreadsheet proposals can be linked to in the interim, until that function is duplicated in the DB.

I guess the inventory part of this can be developed in parallel to the customer info, as it wont be until the proposals are integrated that the two will have a common aspect.

Any thoughts/comment?

I'm part way through reading "Grover Park George ON ACCESS"... can anyone recommend more good books on Access.

[ajetrumpet]

Comment on what exactly, Bryan? There are no questions in your second reply.

Comments on where to start?? If so, you can start anywhere. But if you're working under somebody, obviously you won't want to work on an Access project that simply duplicates Excel work already being done.

If things like that have to come first, do it on the side when you're not on company time. Right?

As far as inventory goes, I don't have an opinion on that, and the reason is simply because everyone does it different. Inventory is so complex, that there is no boilerplate template for it, nor will there probably ever be. I can do it anyway I'm asked to do it of course, but that's only because I'm an actual developer.

[Bryan021]

Comment on what exactly, Bryan? There are no questions in your second reply...

On the strategy of implementing the project in stages (as you did).
The overall project is for a longterm goal, base on projected growth.
Doing further development of any of the individual aspects in Excel will only be redone in Access later anyway, so I figure that time would be better spent getting a start on Access.
Trying to do scheduling in Access right from the start doesn't seem to make sense to me, as it is dependant on a customer base and proposal, so getting them done first makes sense to me, even though that is duplicating what we currently do in Excel.

I can do it anyway I'm asked to do it of course, but that's only because I'm an actual developer.

I would love to be either in the position to farm this out to a true Developer, or to have had the time to train and be in that position myself... but I'm stuck with being a jack of all trades, master of none... luckily, I pick things up pretty quickly, and achieve satisfactory result, thanks in a large part to the help from sites like this.